Posted on Oct 17, 2022 • Edited on Jan 18, 2023

How to re-sync auto-increment in PostgreSQL

First, to check if your auto-increment is out of sync, run the following:

select max(id) from table; select nextval('table_id_seq')

If the result from nextval is not greater than the result of your max(id), your auto-increment is out of sync.

table_id_seq is the name of the sequence, which is composed of table name _ column with auto-increment _ seq. If you have a table purchases where the id is the auto-increment column, then the sequence name would be purchases_id_seq.

If you have your table inside a schema, following the previous example, having your table inside of ecommerce schema, the name would be ecommerce."purchases_id_seq".

You can check all your sequences with the following statement :

select * from information_schema.sequences

Here's the statement to fix it, using our example names:

select setval('ecommerce."purchases_id_seq"', coalesce( (select max(id)+1 from ecommerce.purchases), 1), false)

Hope this was helpful!

Top comments (1)

Peter Ivanov • Dec 22 '24 • Edited

Here is how to do it for all tables that have ID

DO $$ DECLARE tbl RECORD; seq_name TEXT; max_id BIGINT; BEGIN -- Loop through all tables with an `id` column FOR tbl IN SELECT table_schema, table_name FROM information_schema.columns WHERE column_name = 'id' AND table_schema NOT IN ('pg_catalog', 'information_schema') LOOP -- Dynamically construct the sequence name seq_name := format('%I.%I_id_seq', tbl.table_schema, tbl.table_name); -- Check if the sequence exists IF EXISTS ( SELECT 1 FROM information_schema.sequences WHERE sequence_schema = tbl.table_schema AND sequence_name = format('%I_id_seq', tbl.table_name) ) THEN -- Get the max ID value from the table EXECUTE format('SELECT max(id) FROM %I.%I', tbl.table_schema, tbl.table_name) INTO max_id; -- Use COALESCE to handle NULL values for empty tables EXECUTE format( 'SELECT setval(%L, coalesce(%s, 1), false)', seq_name, COALESCE(max_id, 0) + 1 ); END IF; END LOOP; END $$;