PostgreSQL-使用2个自动递增的数字，第二个从1重新启动

Question

我想要一个表，其中对于每个新id，第二个id(例如修订版(从1开始，例如：

id    revision    data
1     1          data1
1     2          data2
2     1          data3
2     2          data4
1     3          data5
3     1          data6

当我想更新数据(行(时，我会保留原来的行，并创建一个具有相同id但具有不同修订的新行。因此，我会跟踪桌面上所做的更改。

我使用以下查询创建表：

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

但是，使用此查询，修订将从其离开的位置继续，例如：id修订数据

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

如何实现这样的表格结构？

Answer 1

在这种情况下不能使用第二个SERIAL-插入新修订版时需要使用MAX(revision) +1：

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

Answer 2

您应该通过引入serial主键来创建表，然后在查询表时计算修订：

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;

另一种选择是使用触发器来分配revision——这在插入时是不必要的开销。