我遇到了一个问题,我的主键序列与我的表行不同步。
也就是说,当我插入一个新行时,我得到一个重复的键错误,因为在串行数据类型中隐含的序列返回一个已经存在的数字。
这似乎是由于导入/恢复没有正确地维护序列造成的。
我遇到了一个问题,我的主键序列与我的表行不同步。
也就是说,当我插入一个新行时,我得到一个重复的键错误,因为在串行数据类型中隐含的序列返回一个已经存在的数字。
这似乎是由于导入/恢复没有正确地维护序列造成的。
当前回答
我找不到Rails的明确答案。
从rails控制台
ActiveRecord::Base.connection.execute("SELECT setval(pg_get_serial_sequence('table_name', 'id'), MAX(id)) FROM table_name;")
例如将table_name替换为users。
其他回答
在下例中,表名为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()将只返回当前值,如本文所述。
这将重置所有来自public的序列,不假设表或列名。在8.4版上测试
CREATE OR REPLACE FUNCTION "reset_sequence" (tablename text, columnname text, sequence_name text)
RETURNS "pg_catalog"."void" AS
$body$
DECLARE
BEGIN
EXECUTE 'SELECT setval( ''' || sequence_name || ''', ' || '(SELECT MAX(' || columnname ||
') FROM ' || tablename || ')' || '+1)';
END;
$body$ LANGUAGE 'plpgsql';
SELECT table_name || '_' || column_name || '_seq',
reset_sequence(table_name, column_name, table_name || '_' || column_name || '_seq')
FROM information_schema.columns where column_default like 'nextval%';
如果您在加载自定义SQL数据进行初始化时看到此错误,另一种避免此错误的方法是:
而不是写:
INSERT INTO book (id, name, price) VALUES (1 , 'Alchemist' , 10),
从初始数据中删除id(主键)
INSERT INTO book (name, price) VALUES ('Alchemist' , 10),
这使Postgres序列保持同步!
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;
重新启动所有序列到1使用:
-- Create Function
CREATE OR REPLACE FUNCTION "sy_restart_seq_to_1" (
relname TEXT
)
RETURNS "pg_catalog"."void" AS
$BODY$
DECLARE
BEGIN
EXECUTE 'ALTER SEQUENCE '||relname||' RESTART WITH 1;';
END;
$BODY$
LANGUAGE 'plpgsql';
-- Use Function
SELECT
relname
,sy_restart_seq_to_1(relname)
FROM pg_class
WHERE relkind = 'S';