Postgres 自动增量没有在显式的 id 插入上更新

我有以下表格在 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。但是在这种情况下，表的自动递增指针没有更新。因此，在第3次插入时，我得到了错误:

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

我从未在 MyISAM 和 INNODB 引擎中遇到过这个问题。不管显式与否，mysql 总是根据 max 行 id 更新自动增量指针。

这个问题的解决办法是什么？我需要它是因为我想更严格地控制我表中的一些 id。

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

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

38686 次浏览
小开
最佳答案

这就是它的工作原理——只有当系统需要这个列的值而您没有提供值时，才调用 next_val('test_id_seq')。如果提供值，则不执行此类调用，因此序列不会“更新”。

您可以通过使用显式提供的值手动 设定处理上次插入后的序列值:

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

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

小开

在 Django 的最新版本中，这个主题在文档中进行了讨论:

Django 使用 PostgreSQL 的 串行数据类型来存储自动递增 SERial 列用来自 序列的值填充 手动分配一个 值添加到自动递增字段时，不会更新该字段的 序列，这可能会在以后引起冲突。

参考文献: < a href = “ https://docs.djangoproject.com/en/dev/Ref/database/# Manual-spected-auto”rel = “ norefrer”> https://docs.djangoproject.com/en/dev/Ref/databases/#manually-specified-autoincrement-pk

还有一个管理命令 manage.py sqlsequencereset app_label ...，它能够生成 SQL 语句，用于重置给定应用程序名称的序列

参考文献: < a href = “ https://docs.djangoproject.com/en/dev/Ref/django-admin/# django-admin-sqlSequencereset”rel = “ norefrer”> 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;
小开

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

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


提交答案