postgres 自动增量未在显式 ID 插入时更新 [英] postgres autoincrement not updated on explicit id inserts

问题描述

我在 postgres 中有下表:

I have the following table in postgres:

CREATE TABLE "test" (
    "id" serial NOT NULL PRIMARY KEY,
    "value" text
)

我正在做以下插入:

insert into test (id, value) values (1, 'alpha')
insert into test (id, value) values (2, 'beta')

insert into test (value) values ('gamma')

在前 2 个插入中，我明确提到了 id.但是，在这种情况下不会更新表的自动增量指针.因此在第三次插入我得到错误:

In the first 2 inserts I am explicitly mentioning the id. However the table's auto increment pointer is not updated in this case. Hence in the 3rd insert I get the error:

ERROR:  duplicate key value violates unique constraint "test_pkey"
DETAIL:  Key (id)=(1) already exists.

我从未在 MyISAM 和 INNODB 引擎中的 Mysql 中遇到过这个问题.显式与否，mysql 总是根据最大行 id 更新自增指针.

I never faced this problem in Mysql in both MyISAM and INNODB engines. Explicit or not, mysql always update autoincrement pointer based on the max row id.

在 postgres 中解决这个问题的方法是什么?我需要它，因为我想对表中的某些 ID 进行更严格的控制.

What is the workaround for this problem in postgres? I need it because I want a tighter control for some ids in my table.

更新:我需要它，因为对于某些值，我需要有一个固定的 ID.对于其他新条目，我不介意创建新条目.

UPDATE: I need it because for some values I need to have a fixed id. For other new entries I dont mind creating new ones.

我认为每当我显式插入 id 时，都可以通过手动将 nextval 指针增加到 max(id) + 1 来实现.但我不知道该怎么做.

I think it may be possible by manually incrementing the nextval pointer to max(id) + 1 whenever I am explicitly inserting the ids. But I am not sure how to do that.

推荐答案

这就是它应该如何工作 - next_val('test_id_seq') 仅在系统需要此列的值时调用，并且你还没有提供.如果您提供值，则不会执行此类调用，因此不会更新"序列.

That's how it's supposed to work - next_val('test_id_seq') is only called when the system needs a value for this column and you have not provided one. If you provide value no such call is performed and consequently the sequence is not "updated".

您可以通过手动设置值来解决此问题使用明确提供的值在最后一次插入之后的序列:

You could work around this by manually setting the value of the sequence after your last insert with explicitly provided values:

SELECT setval('test_id_seq', (SELECT MAX(id) from "test"));

序列的名称是自动生成的，并且始终是tablename_columnname_seq.

The name of the sequence is autogenerated and is always tablename_columnname_seq.

这篇关于postgres 自动增量未在显式 ID 插入时更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！