我遇到了一个问题,我的主键序列与我的表行不同步。
也就是说,当我插入一个新行时,我得到一个重复的键错误,因为在串行数据类型中隐含的序列返回一个已经存在的数字。
这似乎是由于导入/恢复没有正确地维护序列造成的。
我遇到了一个问题,我的主键序列与我的表行不同步。
也就是说,当我插入一个新行时,我得到一个重复的键错误,因为在串行数据类型中隐含的序列返回一个已经存在的数字。
这似乎是由于导入/恢复没有正确地维护序列造成的。
当前回答
丑陋的黑客修复它使用一些贝壳魔法,不是一个伟大的解决方案,但可能会激励其他人有类似的问题:)
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 -
其他回答
试着重建索引。
更新:正如评论中指出的,这是对最初问题的回答。
我花了一个小时试图让djsnowsill的答案与数据库使用Mixed Case表和列一起工作,然后终于偶然发现了解决方案,感谢Manuel Darveau的评论,但我认为我可以让每个人都更清楚一点:
CREATE OR REPLACE FUNCTION "reset_sequence" (tablename text, columnname text)
RETURNS "pg_catalog"."void" AS
$body$
DECLARE
BEGIN
EXECUTE format('SELECT setval(pg_get_serial_sequence(''%1$I'', %2$L),
(SELECT COALESCE(MAX(%2$I)+1,1) FROM %1$I), false)',tablename,columnname);
END;
$body$ LANGUAGE 'plpgsql';
SELECT format('%s_%s_seq',table_name,column_name), reset_sequence(table_name,column_name)
FROM information_schema.columns WHERE column_default like 'nextval%';
这样做的好处是:
不假设ID列是特定的拼写方式。 不假设所有的表都有一个序列。 适用于混合大小写表/列名。 使用更简洁的格式。
解释一下,问题是pg_get_serial_sequence接受字符串来计算你引用的是什么,所以如果你这样做:
"TableName" --it thinks it's a table or column
'TableName' --it thinks it's a string, but makes it lower case
'"TableName"' --it works!
这是使用格式字符串中的“%1$I”实现的,“使撇号1$表示第一个参数,I表示在引号中
-- 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论坛
Pg_get_serial_sequence可以用来避免任何关于序列名称的错误假设。这将在一个镜头中重置序列:
SELECT pg_catalog.setval(pg_get_serial_sequence('table_name', 'id'), (SELECT MAX(id) FROM table_name)+1);
或者更简洁地说:
SELECT pg_catalog.setval(pg_get_serial_sequence('table_name', 'id'), MAX(id)) FROM table_name;
然而,这个表单不能正确地处理空表,因为max(id)是空的,你也不能设置val 0,因为它超出了序列的范围。一种解决方法是使用ALTER SEQUENCE语法。
ALTER SEQUENCE table_name_id_seq RESTART WITH 1;
ALTER SEQUENCE table_name_id_seq RESTART; -- 8.4 or higher
但是ALTER SEQUENCE的用途有限,因为序列名和重启值不能是表达式。
似乎最好的通用解决方案是调用setval,将false作为第三个参数,允许我们指定“下一个要使用的值”:
SELECT setval(pg_get_serial_sequence('t1', 'id'), coalesce(max(id),0) + 1, false) FROM t1;
这符合我所有的条件:
避免硬编码实际的序列名 正确处理空表 使用现有数据处理表,并且不会留下 序列中的漏洞
最后,请注意,pg_get_serial_sequence仅在序列属于列时才有效。如果将递增列定义为串行类型,则会出现这种情况,但是如果手动添加序列,则需要确保ALTER sequence。“OWNED BY”也被执行。
例如,如果使用serial type来创建表,这应该都可以工作:
CREATE TABLE t1 (
id serial,
name varchar(20)
);
SELECT pg_get_serial_sequence('t1', 'id'); -- returns 't1_id_seq'
-- reset the sequence, regardless whether table has rows or not:
SELECT setval(pg_get_serial_sequence('t1', 'id'), coalesce(max(id),0) + 1, false) FROM t1;
但如果手动添加序列:
CREATE TABLE t2 (
id integer NOT NULL,
name varchar(20)
);
CREATE SEQUENCE t2_custom_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER TABLE t2 ALTER COLUMN id SET DEFAULT nextval('t2_custom_id_seq'::regclass);
ALTER SEQUENCE t2_custom_id_seq OWNED BY t2.id; -- required for pg_get_serial_sequence
SELECT pg_get_serial_sequence('t2', 'id'); -- returns 't2_custom_id_seq'
-- reset the sequence, regardless whether table has rows or not:
SELECT setval(pg_get_serial_sequence('t2', 'id'), coalesce(max(id),0) + 1, false) FROM t1;
在下例中,表名为users,模式名为public(默认模式),请根据需要替换它。
1. 检查最大id:
SELECT MAX(id) FROM public.users;
2. 检查下一个值:
SELECT nextval('public."users_id_seq"');
3.如果下一个值小于最大id,重置它:
SELECT setval('public."users_id_seq"',
(SELECT MAX(id) FROM public.users)
);
注意:
Nextval()将在返回当前值之前递增序列,而currval()将只返回当前值,如本文所述。