postgresql postgres autoincrement未在显式id插入时更新

cl25kdpy 于 2023-05-22 发布在 PostgreSQL

关注(0)|答案(4)|浏览(145)

我在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.

我从来没有在MyISAM和INNODB引擎中遇到过这个问题。无论是否显式，mysql总是根据最大行id更新自动增量指针。
在postgres中解决这个问题的方法是什么？我需要它，因为我希望对表中的一些ID进行更严格的控制。

**UPDATE：**我需要它，因为对于某些值，我需要有一个固定的id。对于其他新条目，我不介意创建新的。

我认为，每当我显式插入id时，手动递增指向max(id) + 1的nextval指针是可能的。但我不知道该怎么做。

postgresql

来源：https://stackoverflow.com/questions/9108833/postgres-autoincrement-not-updated-on-explicit-id-inserts

4条答案

按热度按时间

gpnt7bae1#

这就是它的工作原理--next_val('test_id_seq')只有在系统需要该列的值而您没有提供时才会被调用。如果您提供值，则不会执行此类调用，因此序列不会“更新”。
您可以通过手动setting上次插入后的序列值（显式提供的值）来解决此问题：

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

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

赞(0）回复(0）举报 2023-05-22

ih99xse12#

在Django的最新版本中，文档中讨论了这个主题：
Django使用PostgreSQL的SERIAL数据类型来存储自动递增的主键。SERIAL列使用来自sequence的值填充，该值跟踪下一个可用值。手动为自动递增字段分配值不会更新字段的序列，这可能会在以后导致冲突。

**参考：**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;

赞(0）回复(0）举报 2023-05-22

mum43rcc3#

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

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

赞(0）回复(0）举报 2023-05-22

wyyhbhjk4#

与最初的问题略有不同，但现在建议使用IDENTITY列而不是SERIAL。除了一些其他的优点，默认的行为是阻止手动插入。这至少可以防止在意外手动插入的情况下出现问题。然而，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

赞(0）回复(0）举报 2023-05-22

我来回答

postgresql postgres autoincrement未在显式id插入时更新

4条答案

相关问题

热门标签

最新问答