sql - POSTGRES - 使用 ON CONFLICT DO NOTHING 防止串行递增

Question

这个问题在这里已经有了答案:

serial in postgres is being increased even though I added on conflict do nothing (4 个回答)

关闭 2 年前。

duplicate of this question

假设我有下表的东西。我想要表中的唯一名称，因此没有重复项。 插入事物的过程不需要检查具有此名称的事物是否已经存在。

CREATE TABLE things(
    id SMALLSERIAL PRIMARY KEY,
    name varchar UNIQUE
);

当我像这样插入值时它起作用了。如果“desk”已经在 things 中，则不会插入。

INSERT INTO things (name)
VALUES ('desk')
ON CONFLICT DO NOTHING;

唯一的问题是 ON CONFLICT DO NOTHING 并不是真的什么都不做。它仍然会增加 id 字段的序列。

如果这种情况经常发生，id 序列最终会变得对于字段类型来说太大。

有没有办法防止这种情况发生？

Answer 1

使用 insert ... on conflict ，你无法阻止 serial自动增加冲突。 Postgres(就像其他数据库一样)不保证连续序列，如 explained in the documentation :

Because smallserial, serial and bigserial are implemented using sequences, there may be "holes" or gaps in the sequence of values which appears in the column, even if no rows are ever deleted. A value allocated from the sequence is still "used up" even if a row containing that value is never successfully inserted into the table column. This may happen, for example, if the inserting transaction rolls back.

如果您运行大量 insert最终发生冲突，限制出血的一种方法是将语法更改为 not exists :

insert into things (name)
select name
from (values ('desk')) v(name)
where not exists (select 1 from things t1 where t1.name = v.name)

请注意，这仍然不能保证连续剧是连续的(请参阅文档中的上述引用)。