不同步时如何重置postgres的主键序列? [英] How to reset postgres' primary key sequence when it falls out of sync?

问题描述

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

I ran into the problem that my primary key sequence is not in sync with my table rows.

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

That is, when I insert a new row I get a duplicate key error because the sequence implied in the serial datatype returns a number that already exists.

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

It seems to be caused by import/restores not maintaining the sequence properly.

推荐答案

-- 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 论坛

这篇关于不同步时如何重置postgres的主键序列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！