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')

在前两个插入中，我明确提到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.

我认为可以通过手动增加 nextval 来实现每当我显式插入ID时，指向 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"));

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