POSTGRES-使用ON CONFLICT DO NOTHING防止序列递增 [英] POSTGRES - prevent serial incrementation with ON CONFLICT DO NOTHING

问题描述

重复这个问题

说我有以下表格内容.我想要表中的唯一名称，所以没有重复的名称.插入事物的过程无需检查是否已存在具有此名称的事物.

Say I have the following table things. I want unique names in the table so there are no duplicates. The process that inserts a thing shouldn't need to check if a thing with this name is already there.

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

当我插入这样的值时，它起作用.如果办公桌"已经在东西中，则不会插入.

When I insert values like this it works. If 'desk' is already in things it won't be inserted.

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

唯一的问题是冲突.什么都不做"实际上并没有做任何事情.仍然会增加id字段的顺序.

Tho only problem is ON CONFLICT DO NOTHING does not really do nothing. It still increments the sequence for the id field.

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

If this happens too often the id sequence eventually gets too big for the field type.

有办法防止这种情况发生吗?

Is there a way to prevent this from happening?

推荐答案

在冲突时使用 insert ... ，您无法阻止 serial 自动递增在冲突上.Postgres(就像其他数据库一样)不保证序列是连续的，因为在文档中解释:

Using insert ... on conflict, you can't prevent the serial to auto-increment on conflicts. Postgres (just like other databases) does not guarantees that serials are sequential, as explained in the documentation:

由于 smallserial ， serial 和 bigserial 是使用序列实现的，因此可能存在漏洞".或列中出现的值序列中的间隔，即使从未删除任何行也是如此.从序列分配的值仍被用完".即使从未成功将包含该值的行插入到表列中也是如此.例如，如果插入事务回滚，可能会发生这种情况.

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 都以冲突结尾，则限制该指令的一种方法是将语法更改为不存在:

If you are running a lots of insert that end up in conflict, one way to limit the bleading would be to change the syntax to 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)

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

Note that this still does not guarantee that serials will be sequential (refer to the above quote from the documentation).

这篇关于POSTGRES-使用ON CONFLICT DO NOTHING防止序列递增的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！