How to reset postgres' primary key sequence when it falls out of sync?

Question

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.

Answer 1

-- 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; 

Source - Ruby Forum

Answer 2

pg_get_serial_sequence can be used to avoid any incorrect assumptions about the sequence name. This resets the sequence in one shot:

SELECT pg_catalog.setval(pg_get_serial_sequence('table_name', 'id'), (SELECT MAX(id) FROM table_name)+1); 

Or more concisely:

SELECT pg_catalog.setval(pg_get_serial_sequence('table_name', 'id'), MAX(id)) FROM table_name; 

However this form can't handle empty tables correctly, since max(id) is null, and neither can you setval 0 because it would be out of range of the sequence. One workaround for this is to resort to the ALTER SEQUENCE syntax i.e.

ALTER SEQUENCE table_name_id_seq RESTART WITH 1; ALTER SEQUENCE table_name_id_seq RESTART; -- 8.4 or higher 

But ALTER SEQUENCE is of limited use because the sequence name and restart value cannot be expressions.

It seems the best all-purpose solution is to call setval with false as the 3rd parameter, allowing us to specify the "next value to use":

SELECT setval(pg_get_serial_sequence('t1', 'id'), coalesce(max(id),0) + 1, false) FROM t1; 

This ticks all my boxes:

1. avoids hard-coding the actual sequence name
2. handles empty tables correctly
3. handles tables with existing data, and does not leave a hole in the sequence

Finally, note that pg_get_serial_sequence only works if the sequence is owned by the column. This will be the case if the incrementing column was defined as a serial type, however if the sequence was added manually it is necessary to ensure ALTER SEQUENCE .. OWNED BY is also performed.

i.e. if serial type was used for table creation, this should all work:

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; 

But if sequences were added manually:

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;