POSTGRES sequence TRIGGER on non primary key

Question

I have a PostgreSQL database Version 9.5.0.

The table UserComments was created like:

create table UserComments (  
  userId integer, 
  commentNr integer, 
  comment text,  
  UNIQUE (userId, commentNr)
);

The table cannot be changed neither can I add an extra table.

I would like to make a sequence (in other words: an auto-incrementing value) on commentNr depending on the amount of userId entries. For each new entry with the same userId its value should be incremented.
Example:

userId | commentNr | comment
-------|-----------|---------
1      |   1       | blabla
1      |   2       | more bla
2      |   1       | myownbla
1      |   3       | evenmorebla

I have already found some answers with "triggers" and "window functions" but none did really work for me since I am not allowed to change the table. Best solution would be a trigger on each INSERT that automatically counts/computes the commentNr so I can add a new entry with

insert into user(userId, comment) values ('1', 'bla');

Any help would be appreciated.

Answer 1

To auto calculating commentNr field you can use TRIGGER:

CREATE INDEX i1 ON UserComments (userId, commentNr DESC);

CREATE OR REPLACE FUNCTION set_commentNr() RETURNS TRIGGER AS $sql$
BEGIN
    NEW.commentNr = coalesce(max(commentNr) + 1, 1) FROM UserComments WHERE UserId = NEW.userID;
    RETURN NEW;
END;
$sql$ LANGUAGE plpgsql STABLE;


CREATE TRIGGER ins_comment BEFORE INSERT ON UserComments FOR EACH ROW EXECUTE PROCEDURE set_commentNr();

Small test:

insert into userComments (userId, comment) values ('1', 'bla');
insert into userComments (userId, comment) values ('1', 'bla');
insert into userComments (userId, comment) values ('2', 'bla');
insert into userComments (userId, comment) values ('1', 'bla');
insert into userComments (userId, comment) values ('2', 'bla');

SELECT * FROM userComments;

 userid | commentnr | comment 
--------+-----------+---------
      1 |         1 | bla
      1 |         2 | bla
      2 |         1 | bla
      1 |         3 | bla
      2 |         2 | bla
(5 rows)

Index i1 created to increase performance for calculating next value:

                                                             QUERY PLAN                                                              
-------------------------------------------------------------------------------------------------------------------------------------
 Result  (cost=4.17..4.18 rows=1 width=4) (actual time=0.032..0.032 rows=1 loops=1)
   InitPlan 1 (returns $0)
     ->  Limit  (cost=0.15..4.17 rows=1 width=4) (actual time=0.026..0.026 rows=1 loops=1)
           ->  Index Only Scan using i1 on usercomments  (cost=0.15..24.27 rows=6 width=4) (actual time=0.021..0.021 rows=1 loops=1)
                 Index Cond: ((userid = 1) AND (commentnr IS NOT NULL))
                 Heap Fetches: 1
 Planning time: 0.238 ms
 Execution time: 0.095 ms

Answer 2

For a simple case such as this, it is not necessary to create a trigger. The proper sequence can be calculated in a CTE or subquery and inserted.

Note: If using the sql below, the inserted userId has to be specified twice

INSERT INTO "user" ("userId", "commentNr", "comment") (
WITH 
  nextval AS (
  SELECT COALESCE(MAX("commentNr"),0)+1 commentNr
  FROM "user"
  WHERE "userId" = '1'
)
SELECT '1', nextval.commentNr, 'bla'
FROM nextval
);