PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Custom id sequence postgres

I have a table:

CREATE TABLE notes (
  id INT NOT NULL,
  user_id int NOT NULL,
  text varchar NOT NULL,
  primary key(id, user_id)
);

so I need postgres to create ids of records unique by user, but not by whole table. each user must have a record with id 1, 2, 3, etc.

example:

id user_id text
1 1 any user’s text
2 1 any user’s text
3 1 any user’s text
1 2 any user’s text
2 2 any user’s text
3 2 any user’s text
4 1 any user’s text
5 1 any user’s text
4 2 any user’s text

in this example every user has his own sequence of ids his records, and the other user does not affect the sequence of his IDs.

in other words each user has his own records with separate ids like this user is the only person who creates any records.

for example, if user1 has 10 records, and then user2 created one records, id of this record must be 1, not 11 (because this is the first record of user2).

I’ve tried to create functions, triggers, sequences, but this did not give the desired result.

here is an example of what i’ve tried:

CREATE OR REPLACE FUNCTION note_id() RETURNS trigger as 
$$
 BEGIN
    NEW.id = (select max(id) from notes where user_id = new.user_id) + 1;
 END; 
$$
LANGUAGE plpgsql;
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    solution:

    CREATE OR REPLACE FUNCTION set_note_id()
RETURNS TRIGGER AS $f$
DECLARE
max_identifier INTEGER;
BEGIN
  SELECT MAX(id)+1 INTO max_identifier
  FROM test.notes
  WHERE user_id = NEW.user_id;
IF max_identifier IS NULL THEN
    max_identifier := 1;
END IF;
NEW.id := max_identifier;
RETURN NEW;
END;
$f$ LANGUAGE plpgsql;

CREATE TRIGGER before_insert_trigger_notes
BEFORE INSERT ON test.notes
FOR EACH ROW
EXECUTE FUNCTION set_note_id();

  2. 0

    Try the following updated PostgreSQL function and trigger

    Function>>

       CREATE OR REPLACE FUNCTION note_id() RETURNS TRIGGER AS $$
BEGIN
    -- Obtain a session-level advisory lock based on user_id
    PERFORM pg_advisory_lock(NEW.user_id);

    -- Calculate the next id
    SELECT COALESCE(MAX(id), 0) + 1 INTO NEW.id FROM notes WHERE user_id = NEW.user_id;

    -- Release the lock
    PERFORM pg_advisory_unlock(NEW.user_id);

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

    Trigger –>

    CREATE TRIGGER set_note_id
BEFORE INSERT ON notes
FOR EACH ROW
EXECUTE FUNCTION note_id();
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search