postgresqlcomposite-primary-keydatabase-sequence

How to auto increment one column of a postgres composite primary key?


I am making a simple note app, and I want to create a table to track a note's edit history and save them with an incrementing version as a composite key with the note's id. So for example:

note_id | version | content
----------------------------
    1   |    1    | some_text
    1   |    2    | some_text 
    2   |    1    | some_text
    2   |    2    | some_text 
    1   |    3    | some_text 
    3   |    1    | some_text 
    1   |    4    | some_text 

The composite key is made up of note_id and version I want to increment version for each note_id Is there a way to automate this? I have seen some other posts, and experimented with triggers and functions, but I have not yet been able to get anything to work. Any advice is greatly appreciated!


Solution

  • Cool I actually got this to work, hopefully this can help someone else in the future. Thanks to this answer which I made some slight tweaks too, but mostly it just worked.

    --Logic to track and automatically increment a note's version number in the notes_history table
    --A table to track the current version of each note
    CREATE TABLE notes_version_counter(
      note_id UUID PRIMARY KEY REFERENCES users(id),
      current_version INTEGER NOT NULL
    );
    
    --A function to calculate the next version for a note
    CREATE FUNCTION next_version(p_note_id UUID)
      RETURNS INTEGER
    AS
    $$
      INSERT INTO notes_version_counter (note_id, current_version)
      VALUES (p_note_id, 1)
      ON CONFLICT (note_id)
      DO UPDATE
        SET current_version = notes_version_counter.current_version + 1
      RETURNING current_version;
    $$
    language sql
    volatile;
    
    --A function to increment the version of a note
    CREATE FUNCTION increment_version()
      RETURNS TRIGGER
    AS
    $$
    BEGIN
      new."note_version" := next_version(new."note_id");
      RETURN new;
    END;
    $$
    language plpgsql;
    
    --A trigger when inserting a note into the history table to call the increment_version function
    CREATE TRIGGER TR_notes_history_increment_version
      BEFORE INSERT ON notes_history
      FOR EACH ROW
      EXECUTE PROCEDURE increment_version();
    

    And for reference, here are my main tables

    CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
    
    CREATE TABLE users (
      id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
      username varchar(20) NOT NULL UNIQUE,
      password varchar(20)
    );
    
    CREATE TABLE notes (
      id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
      user_id UUID REFERENCES users(id) NOT NULL,
      title VARCHAR(100),
      note VARCHAR(5000),
      tag VARCHAR(100),
      created TIMESTAMPTZ NOT NULL,
      edited TIMESTAMPTZ NOT NULL
    );
    
    CREATE TABLE notes_history (
      note_id UUID REFERENCES users(id) NOT NULL,
      note_version INTEGER NOT NULL,
      title_historic VARCHAR(100),
      note_historic VARCHAR(5000),
      tag_historic VARCHAR(100),
      edited TIMESTAMPTZ NOT NULL,
      PRIMARY KEY(note_id, note_version)
    );