Reset PostgreSQL Sequences After Data Import - This PL/pgSQL script dynamically resets all sequences in a PostgreSQL database to match the current maximum value of the primary key column they are associated with.

sync-sequences.sql

DO $$
DECLARE
    seq_name TEXT;
    tbl_name TEXT;
    col_name TEXT;
    max_id BIGINT;
BEGIN
    FOR seq_name, tbl_name, col_name IN 
        SELECT 
            s.relname AS seq_name,
            t.relname AS tbl_name,
            a.attname AS col_name
        FROM pg_class s
        JOIN pg_depend d ON d.objid = s.oid
        JOIN pg_class t ON t.oid = d.refobjid
        JOIN pg_attribute a ON a.attrelid = t.oid AND a.attnum = d.refobjsubid
        WHERE s.relkind = 'S' AND d.deptype = 'a'
    LOOP
        EXECUTE format('SELECT COALESCE(MAX(%I), 0) FROM %I.%I', col_name, 'public', tbl_name) INTO max_id;
        RAISE NOTICE 'Updating sequence % to %', seq_name, max_id + 1;
        EXECUTE format('SELECT setval(''%I'', %s)', seq_name, max_id + 1);
    END LOOP;
END;
$$ LANGUAGE plpgsql;