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

Question

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!

Answer 1

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)
);

Answer 2

Keep in mind with this approach the id's and versions have to be searched for each insert, so it's good to have an index to speed that up. To avoid constant searches of the main table, you can maintain a table with latest version for each note_id. The search still has to take place, but there's less to search through: demo

create table latest_note_versions(
  note_id int primary key,
  version int default 1          );

create function tgf_notes_version() returns trigger language plpgsql as $f$ 
begin 
  with version_upsert as (
      insert into latest_note_versions AS lnv 
      values (NEW.note_id)
      on conflict(note_id) do update set version=lnv.version+1
      returning version)
  select version from version_upsert into NEW.version;
  return NEW;
end $f$;

create trigger tg_a_gen before insert on notes
for each row execute function tgf_notes_version();

Now you can insert nothing or anything into the version column and the trigger will take care of it:

insert into notes values 
  (1,999,'my_own_text11'), 
  (1,999,'my_own_text12'), 
  (3,123,'my_own_text31'), 
  (3,321,'my_own_text32'), 
  (3,0,'my_own_text33')
returning *;

note_id	version	content
1	1	my_own_text11
1	2	my_own_text12
3	1	my_own_text31
3	2	my_own_text32
3	3	my_own_text33

You need to formulate your own rules on what to do if someone comes in and deletes or updates something in the table, or remember not to rely on the continuity of version sequences - which is actually a good advice for any sequence due to caching and transaction behaviour.