PostgreSQL自增序列在显式插入ID时不会更新

Question

我在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。然而，在这种情况下，表的自增指针未更新。因此，在第三个插入操作中，我会收到错误提示：

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

在MySQL的MyISAM和INNODB引擎中，我从未遇到过这个问题。无论是否显式指定，Mysql总是基于最大行ID更新自动增量指针。

那么，在Postgres中如何解决这个问题呢？我需要解决此问题，因为我想对表中的某些id进行更严格的控制。

更新: 我需要这样做是因为对于某些值，我需要一个固定的id。对于其他新条目，我不介意创建新条目。

我认为，当我显式插入id时，可以通过手动将nextval指针递增到max(id) + 1来实现。但我不确定如何做到这一点。

- jerrymouse

Answer 1

这就是它应该工作的方式 - 当系统需要这个列的值且您未提供值时，next_val('test_id_seq') 才会被调用。如果您提供了值，则不会执行此类调用，因此序列不会“更新”。

您可以通过在最后一次插入明确提供值后手动设置序列的值来解决这个问题：

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

该序列的名称是自动生成的，始终为tablename_columnname_seq。

- Milen A. Radev

---

奇怪的行为。我也是从MySQL转过来的。谢谢！ - Mark Stahler

我遇到了同样的问题，在你和http://www.ruby-forum.com/topic/64428#72333（你们基本上说了同样的话）的帮助下，我的问题得到了解决。谢谢。 - marsol0x

我已经完成了所有这些工作，包括表中的ID和序列最后停留在3，但是当我发布新数据时，完整性错误会引发说ID为2的违反唯一约束条件？我破坏了我的数据库吗？ - reindeer

Answer 2

在最近版本的Django中，文档中讨论了这个主题：

Django使用PostgreSQL的SERIAL数据类型来存储自增主键。SERIAL列由一个序列提供值，该序列跟踪下一个可用值。手动分配自增字段的值不会更新字段的序列，这可能会导致冲突。

参考链接：https://docs.djangoproject.com/en/dev/ref/databases/#manually-specified-autoincrement-pk

还有一个管理命令manage.py sqlsequencereset app_label ...，可以为给定应用程序名称生成重置序列的SQL语句。

参考： https://docs.djangoproject.com/en/dev/ref/django-admin/#django-admin-sqlsequencereset

例如，这些 SQL 语句是由 manage.py sqlsequencereset my_app_in_my_project 生成的：

BEGIN;
SELECT setval(pg_get_serial_sequence('"my_project_aaa"','id'), coalesce(max("id"), 1), max("id") IS NOT null) FROM "my_project_aaa";
SELECT setval(pg_get_serial_sequence('"my_project_bbb"','id'), coalesce(max("id"), 1), max("id") IS NOT null) FROM "my_project_bbb";
SELECT setval(pg_get_serial_sequence('"my_project_ccc"','id'), coalesce(max("id"), 1), max("id") IS NOT null) FROM "my_project_ccc";
COMMIT;

- illagrenan

Answer 3

可以使用触发器自动完成。这样，您可以确保最大值始终用作下一个默认值。

CREATE OR REPLACE FUNCTION set_serial_id_seq()
RETURNS trigger AS
$BODY$
  BEGIN
   EXECUTE (FORMAT('SELECT setval(''%s_%s_seq'', (SELECT MAX(%s) from %s));',
   TG_TABLE_NAME,
   TG_ARGV[0],
   TG_ARGV[0],
   TG_TABLE_NAME));
    RETURN OLD;
   END;
$BODY$
LANGUAGE plpgsql;  

CREATE TRIGGER set_mytable_id_seq
AFTER INSERT OR UPDATE OR DELETE
ON mytable
FOR EACH STATEMENT
  EXECUTE PROCEDURE  set_serial_id_seq('mytable_id');

该功能可用于多个表的重复使用。将“mytable”更改为所需的表格。

有关触发器的更多信息：

https://www.postgresql.org/docs/9.1/plpgsql-trigger.html

https://www.postgresql.org/docs/9.1/sql-createtrigger.html

- jsch

Answer 4

略微偏离原问题，但现在推荐使用 IDENTITY 列而不是 SERIAL。除了其他一些优点外，IDENTITY 列的默认行为是阻止手动插入，这至少可以防止意外手动插入的问题。然而，OP 的问题需要插入特定的数字，所以在此并不是一个真正的解决方案。

https://www.postgresql.org/docs/15/sql-createtable.html

PostgreSQL：serial vs identity

https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_serial

- jsch