更新 postgres 中主键的顺序，用于包含大量删除的表

Question

我有以下问题：我有一个表，里面有很多删除和插入的行。我还想为表中的每一行分配一些 ID 号。目前我正在尝试这样做

DROP SEQUENCE IF EXISTS market_orders_seq
CREATE SEQUENCE market_orders_seq CACHE 1
CREATE TABLE market_orders (id int NOT NULL DEFAULT nextval('market_orders_seq') PRIMARY KEY, typ varchar(5), tag varchar(30), owner_id int, owner_tag varchar(5), amount int, price int, market_id int)
ALTER SEQUENCE market_orders_seq OWNED BY market_orders.id

但是如果我理解正确的话，序列是单调的，当我删除一些行时无法下降，所以我遇到了 id 膨胀得很快的问题。这个问题的解决方案是什么？我想使用第一个未使用的 id 进行插入，但我不知道该怎么做。

Answer 1

虽然这在技术上是可行的，但我实际上不建议这样做。

首先，一个整数值可以存储多达 2 个大约十亿的值，您可能不会命中 - 您仍然可以切换到bigint，这可能会达到大约1^19.

此外，识别间隙需要扫描工作台上的每个插入，这是低效的(工作台越大，效率越低(。

insert into market_orders(id, typ, ...)
select
min(id) + 1,
'foo',
...
from market_orders mo
where not exists(select 1 from market_orders mo1 where mo1.id = mo + 1)

旁注：您应该使用[big]serial数据类型，因此您无需自己处理序列。