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

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

这似乎是由于导入/恢复没有正确地维护序列造成的。


当前回答

我找不到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()将只返回当前值,如本文所述。

如果您在加载自定义SQL数据进行初始化时看到此错误,另一种避免此错误的方法是:

而不是写:

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

从初始数据中删除id(主键)

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

这使Postgres序列保持同步!

所以我可以告诉你,在这个帖子里没有足够的意见或重新发明的轮子,所以我决定让事情变得有趣。

下面是一个程序:

关注(仅影响)与表相关的序列 适用于串行和生成的AS身份列 适用于good_column_names和“BAD_column_123”名称 如果表为空,则自动分配相应序列定义的起始值 允许只影响特定的序列(在schema.table.column表示法中) 有预览模式

CREATE OR REPLACE PROCEDURE pg_reset_all_table_sequences(
    IN commit_mode BOOLEAN DEFAULT FALSE
,   IN mask_in TEXT DEFAULT NULL
) AS
$$
DECLARE
    sql_reset TEXT;
    each_sec RECORD;
    new_val TEXT;
BEGIN

sql_reset :=
$sql$
SELECT setval(pg_get_serial_sequence('%1$s.%2$s', '%3$s'), coalesce(max("%3$s"), %4$s), false) FROM %1$s.%2$s;
$sql$
;

FOR each_sec IN (

    SELECT
        quote_ident(table_schema) as table_schema
    ,   quote_ident(table_name) as table_name
    ,   column_name
    ,   coalesce(identity_start::INT, seqstart) as min_val
    FROM information_schema.columns
    JOIN pg_sequence ON seqrelid = pg_get_serial_sequence(quote_ident(table_schema)||'.'||quote_ident(table_name) , column_name)::regclass
    WHERE
        (is_identity::boolean OR column_default LIKE 'nextval%') -- catches both SERIAL and IDENTITY sequences

    -- mask on column address (schema.table.column) if supplied
    AND coalesce( table_schema||'.'||table_name||'.'||column_name = mask_in, TRUE )
)
LOOP

IF commit_mode THEN
    EXECUTE format(sql_reset, each_sec.table_schema, each_sec.table_name, each_sec.column_name, each_sec.min_val) INTO new_val;
    RAISE INFO 'Resetting sequence for: %.% (%) to %'
        ,   each_sec.table_schema
        ,   each_sec.table_name
        ,   each_sec.column_name
        ,   new_val
    ;
ELSE
    RAISE INFO 'Sequence found for resetting: %.% (%)'
        ,   each_sec.table_schema
        ,   each_sec.table_name
        ,   each_sec.column_name
    ;
END IF
;

END LOOP;

END
$$
LANGUAGE plpgsql
;

预览:

调用pg_reset_all_table_sequences ();

提交:

调用pg_reset_all_table_sequences(真正的);

只指定你的目标表:

调用pg_reset_all_table_sequences(“schema.table.column”);

把它们放在一起

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中是必要的)。

我花了一个小时试图让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表示在引号中