postgresql postgres 自动增量未在显式 ID 插入时更新
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9108833/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
postgres autoincrement not updated on explicit id inserts
提问by jerrymouse
I have the following table in postgres:
我在 postgres 中有下表：
CREATE TABLE "test" (
"id" serial NOT NULL PRIMARY KEY,
"value" text
)
I am doing following insertions:
我正在做以下插入：
insert into test (id, value) values (1, 'alpha')
insert into test (id, value) values (2, 'beta')
insert into test (value) values ('gamma')
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:
在前 2 个插入中，我明确提到了 id。但是，在这种情况下不会更新表的自动增量指针。因此在第三次插入我得到错误：
ERROR: duplicate key value violates unique constraint "test_pkey"
DETAIL: Key (id)=(1) already exists.
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.
在 MyISAM 和 INNODB 引擎中，我从未在 Mysql 中遇到过这个问题。显式与否，mysql 总是根据最大行 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.
postgres 中此问题的解决方法是什么？我需要它，因为我想对表中的某些 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。对于其他新条目，我不介意创建新条目。
I think it may be possible by manually incrementing the nextvalpointer to max(id) + 1whenever I am explicitly inserting the ids. But I am not sure how to do that.
我认为每当我显式插入 id 时nextval，max(id) + 1都可以通过手动增加指针来实现。但我不知道该怎么做。
回答by Milen A. Radev
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".
这就是它应该如何工作 -next_val('test_id_seq')仅在系统需要此列的值而您尚未提供值时才调用。如果您提供值，则不会执行此类调用，因此不会“更新”序列。
You could work around this by manually settingthe value of the sequence after your last insert with explicitly provided values:
您可以通过在上次插入后使用显式提供的值手动设置序列的值来解决此问题：
SELECT setval('test_id_seq', (SELECT MAX(id) from "test"));
回答by illagrenan
In the recent version of Django, this topic is discussed in the documentation:
在最新版本的 Django 中，文档中讨论了这个主题：
Django uses PostgreSQL's SERIAL data typeto store auto-incrementing primary keys. A SERIAL column is populated with values from a sequencethat keeps track of the next available value. Manually assigning a value to an auto-incrementing field doesn't update the field's sequence, which might later cause a conflict.
Django 使用 PostgreSQL 的SERIAL 数据类型来存储自动递增的主键。SERIAL 列填充有来自 跟踪下一个可用值的序列的值。手动为自动递增字段分配值不会更新字段的序列，这可能会在以后导致冲突。
Ref:https://docs.djangoproject.com/en/dev/ref/databases/#manually-specified-autoincrement-pk
参考：https : //docs.djangoproject.com/en/dev/ref/databases/#manually-specified-autoincrement-pk
There is also management command manage.py sqlsequencereset app_label ...that is able to generate SQL statements for resetting sequences for the given app name(s)
还有管理命令manage.py sqlsequencereset app_label ...能够生成用于重置给定应用程序名称的序列的 SQL 语句
Ref:https://docs.djangoproject.com/en/dev/ref/django-admin/#django-admin-sqlsequencereset
参考：https : //docs.djangoproject.com/en/dev/ref/django-admin/#django-admin-sqlsequencereset
For example these SQL statements were generated by manage.py sqlsequencereset my_app_in_my_project:
例如，这些 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;
