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!

Comments

[Peter Ivanov]

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