当postgres的主键序列不同步时，如何重置？

我遇到了我的主键序列与我的表行不同步的问题。

也就是说，当我插入新行时，我得到一个重复的键错误，因为串行数据类型中隐含的序列返回了一个已经存在的数字。

这似乎是由于导入/还原未正确维护序列引起的。

---

#1楼

放在一起

CREATE OR REPLACE FUNCTION "reset_sequence" (tablename text) 
RETURNS "pg_catalog"."void" AS
$body$
DECLARE
BEGIN
  EXECUTE 'SELECT setval( pg_get_serial_sequence(''' || tablename || ''', ''id''),
  (SELECT COALESCE(MAX(id)+1,1) FROM ' || tablename || '), false)';
END;
$body$  LANGUAGE 'plpgsql';

将修复给定表的“ id'序列（例如，通常对于django是必需的）。

---

#2楼

重置所有序列，除了每个表的主键均为“ id”外，无需假名：

CREATE OR REPLACE FUNCTION "reset_sequence" (tablename text, columnname text)
RETURNS "pg_catalog"."void" AS
$body$
DECLARE
BEGIN
    EXECUTE 'SELECT setval( pg_get_serial_sequence(''' || tablename || ''', ''' || columnname || '''),
    (SELECT COALESCE(MAX(id)+1,1) FROM ' || tablename || '), false)';
END;
$body$  LANGUAGE 'plpgsql';

select table_name || '_' || column_name || '_seq', reset_sequence(table_name, column_name) from information_schema.columns where column_default like 'nextval%';

---

#3楼

丑陋的黑客使用一些外壳魔术来解决它，这不是一个很好的解决方案，但可能会激发其他有类似问题的人:)

pg_dump -s <DATABASE> | grep 'CREATE TABLE' | awk '{print "SELECT setval(#" $3 "_id_seq#, (SELECT MAX(id) FROM " $3 "));"}' | sed "s/#/'/g" | psql <DATABASE> -f -

---

#4楼

最快捷的方法：

SELECT setval('tbl_tbl_id_seq', max(tbl_id)) FROM tbl;

tbl_id是表tbl的serial列，从序列tbl_tbl_id_seq （这是默认的自动名称） tbl_tbl_id_seq 。

如果您不知道附加序列的名称（不一定是默认格式），请使用pg_get_serial_sequence() ：

SELECT setval(pg_get_serial_sequence('tbl', 'tbl_id'), max(tbl_id)) FROM tbl;

这里没有一一对应的错误。 每个文档：

两参数形式将序列的last_value字段设置为指定值，并将其is_called字段设置为true，这意味着下一个nextval将在返回值之前使序列前进 。

大胆强调我的。

如果表可以为空， 并且在这种情况下实际上从1开始：

SELECT setval(pg_get_serial_sequence('tbl', 'tbl_id')
            , COALESCE(max(tbl_id) + 1, 1)
            , false)
FROM tbl;

我们不能只使用2参数形式并以0开头，因为默认情况下序列的下限是1 （除非自定义）。

并发

尚无防范并发序列活动或上述查询中写入表的措施。 如果相关，您可以将表锁定为独占模式。 当您尝试同步时，它可以防止并发事务写入更多的数字。 （它还会临时阻止无害的写入，而不会干扰最大数量。）

但这并没有考虑到客户端可能已经预先获取了序列号而在主表上没有任何锁的情况（这可能会发生）。 为此，也只增加序列的当前值，而不要减少它。 看起来可能有些偏执，但这与序列的性质以及防止并发问题保持一致。

BEGIN;

LOCK TABLE tbl IN EXCLUSIVE MODE;

SELECT setval('tbl_tbl_id_seq', max(tbl_id))
FROM   tbl
HAVING max(tbl_id) > (SELECT last_value FROM tbl_tbl_id_seq);

COMMIT;

---

#5楼

如果在加载用于初始化的自定义SQL数据时看到此错误，则另一种避免这种情况的方法是：

而不是写：

INSERT INTO book (id, name, price) VALUES (1 , 'Alchemist' , 10),

从初始数据中删除id （主键）

INSERT INTO book (name, price) VALUES ('Alchemist' , 10),

这样可以使Postgres序列保持同步！

---

#6楼

尝试重新索引 。

更新：如评论中所指出，这是对原始问题的答复。

---

#7楼

-- Login to psql and run the following

-- What is the result?
SELECT MAX(id) FROM your_table;

-- Then run...
-- This should be higher than the last result.
SELECT nextval('your_table_id_seq');

-- If it's not higher... run this set the sequence last to your highest id. 
-- (wise to run a quick pg_dump first...)

BEGIN;
-- protect against concurrent inserts while you update the counter
LOCK TABLE your_table IN EXCLUSIVE MODE;
-- Update the sequence
SELECT setval('your_table_id_seq', COALESCE((SELECT MAX(id)+1 FROM your_table), 1), false);
COMMIT;

来源-Ruby论坛

---

#8楼

该命令仅用于更改PostgreSQL中自动生成的键序列值

ALTER SEQUENCE "your_sequence_name" RESTART WITH 0;

代替零，您可以输入要从其重新启动序列