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

-- 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 counterLOCK TABLE your_table IN EXCLUSIVE MODE;-- Update the sequenceSELECT setval('your_table_id_seq', COALESCE((SELECT MAX(id)+1 FROM your_table), 1), false);COMMIT;

Source - Ruby Forum

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

The shortest and fastest way:

SELECT setval('tbl_tbl_id_seq', max(tbl_id)) FROM tbl;

tbl_id being the serial column of table tbl, drawing from the sequence tbl_tbl_id_seq (which is the default automatic name).

If you don't know the name of the attached sequence (which doesn't have to be in default form), use pg_get_serial_sequence():

SELECT setval(pg_get_serial_sequence('tbl', 'tbl_id'), max(tbl_id)) FROM tbl;

There is no off-by-one error here. The manual:

The two-parameter form sets the sequence's last_value field to thespecified value and sets its is_called field to true, meaning that thenext nextval will advance the sequence before returning a value.

Bold emphasis mine.

If the table can be empty and to actually start from 1 in this case:

SELECT setval(pg_get_serial_sequence('tbl', 'tbl_id')            , COALESCE(max(tbl_id) + 1, 1)            , false)FROM tbl;

We can't just use the 2-parameter form and start with 0 because the lower bound of sequences is 1 by default (unless customized).

Concurrency

To defend against concurrent sequence activity or writes to the table in the above queries, lock the table in SHARE mode. It keeps concurrent transactions from writing a higher number (or anything at all).

To also take clients into account that may have fetched sequence numbers in advance without any locks on the main table, yet (can happen in certain setups), only increase the current value of the sequence, never decrease it. It may seem paranoid, but that's in accord with the nature of sequences and defending against concurrency issues.

BEGIN;LOCK TABLE tbl IN SHARE MODE;SELECT setval('tbl_tbl_id_seq', max(tbl_id))FROM   tblHAVING max(tbl_id) > (SELECT last_value FROM tbl_tbl_id_seq); -- prevent lower numberCOMMIT;

SHARE mode is strong enough for the purpose. The manual:

This mode protects a table against concurrent data changes.

It conflicts with ROW EXCLUSIVE mode.

The commands UPDATE, DELETE, and INSERT acquire this lock mode on the target table