PostgreSQL - Using 2 auto-incremented numbers, the 2nd one restarts from 1

Question

I want a table, where for every new id, the 2nd id(e.g. revision) starts from 1, such as:

id    revision    data

1     1          data1
1     2          data2
2     1          data3
2     2          data4
1     3          data5
3     1          data6

When I want to update the data(the row), I keep the original row, and create a new row with the same id, but with different revision. So I keep track of changes done on the table.

I create the table with this query:

create table my_table (
    id serial not null,
    revision serial not null,
    data jsonb not null
    primary key(id, revision)
);

However, with this query, revision continues from just where it left, such as: id revision data

1     1          data1
1     2          data2
2     3          data3
2     4          data4
1     5          data5
3     6          data6

How can I achieve such a table structure?

Why you need it? You can get a sequential number with `row_number`. — Serg, Mar 31 '21 at 10:04

score 1 · Answer 1 · answered Mar 31 '21 at 10:16

You can't use a second SERIAL in this case - you need to use MAX(revision) +1 when inserting the new revision:

INSERT INTO my_table(id,revision,data) 
  SELECT id,1+COALESCE((SELECT MAX(revision) FROM my_table WHERE id = xxx),0),your_new_data 
  FROM my_table
  WHERE id = xxx

Gordon Linoff · Answer 2 · 2021-03-31T12:38:27.007

1

You should create the table as by introducing a serial primary key and then calculating the revision when you query the table:

create table my_table (
    my_table_id id serial primary key,
    id int not null,
    data jsonb not null
);

create view v_my_table as
    select t.*,
           row_number() over (partition by id order by my_table_id) as revision
    from my_table t;

The alternative would be using a trigger to assign revision -- that is unnecessary overhead on inserts.

edited Mar 31 '21 at 12:38

answered Mar 31 '21 at 11:15

Gordon Linoff

1,242,037
58
646
786

I would add a column `revision_timestamp TIMESTAMP NOT NULL DEFAULT NOW()` to the table and change the view code to order by `revision_timestamp` to be more deterministic, otherwise you don't specify which is the order of the rows within the ones having the same `id` – Ftisiot Mar 31 '21 at 11:27
@Ftisiot . . . I fixed the answer. The intention was to use `my_table_id` for the ordering. – Gordon Linoff Mar 31 '21 at 12:38

PostgreSQL - Using 2 auto-incremented numbers, the 2nd one restarts from 1

2 Answers2