Inserting into Sequence/Serial Table Causes "duplicate key violates unique constraint" Error

如果你在自增表上收到以下错误：

🌐 If you are receiving the below error for an auto-incremented table:

错误：重复的键违反了唯一约束

这很可能意味着表的序列 somehow 出现了不同步的情况，很可能是因为批量导入过程（或者类似的情况）导致的。

🌐 it likely means that the table's sequence has somehow become out of sync, likely because of a mass import process (or something along those lines).

叫它“设计缺陷”吧，但似乎在从转储文件恢复后，你必须手动重置主键索引。

🌐 Call it a "bug by design", but it seems that you have to manually reset the a primary key index after restoring from a dump file.

你可以在你的实例上运行以下命令，看看你的序列是否不同步：

🌐 You can run the following commands on your instance to see if your sequence is out-of-sync:

1
SELECT MAX(<sequenced_column>) FROM <table_name>;
2
3
SELECT nextval(pg_get_serial_sequence('<public.table_name>', '<sequenced_column_name>'));

如果数值偏差超过1，你需要重新同步你的序列。

🌐 If the values are off by more than 1, you need to resynchronize your sequence.

作为预防措施，请通过在常规设置中重启来备份你的PG数据库。当你恢复数据库时，你将有一个保存的备份。或者，你也可以选择下载你的属性表作为备份。

🌐 Back up your PG database by restarting in the General Settings as a precaution. When you restore your database, you will have a backup saved. Alternatively, you can also download your properties table instead as a backup.

然后你可以运行这个：

🌐 Then you can run this:

1
SELECT SETVAL('public.<table_name>_<column_nam>_seq', (SELECT MAX(<column_name>) FROM <table_name>)+1);

这会把序列设置为下一个可用值，该值大于序列中任何现有的主键。

🌐 That will set the sequence to the next available value that's higher than any existing primary key in the sequence.