Fixing Duplicate Key Errors in PostgreSQL After a Backup Restore

Hey folks! Ever restored a PostgreSQL database from a backup only to hit a frustrating "duplicate key value violates unique constraint" error when you try to insert new records? Yeah, it’s a pain! The problem usually lies with auto-incrementing sequences not being updated correctly during the restore. But don’t worry, I’ve got a nifty script that’ll check every table and set the right auto-increment values, so you can get back to business without tearing your hair out.

Just run this query:

DO $$ 
DECLARE
    rec RECORD;
    seq_name TEXT;
    max_id BIGINT;
BEGIN
    FOR rec IN 
        SELECT 
            table_name, 
            column_name, 
            column_default 
        FROM 
            information_schema.columns 
        WHERE 
            column_default LIKE 'nextval%'
    LOOP
        seq_name := regexp_replace(rec.column_default, 'nextval\(''(.*)''::regclass\)', '\1');
        EXECUTE format('SELECT COALESCE(MAX(%I), 0) + 1 FROM %I', rec.column_name, rec.table_name) INTO max_id;
        EXECUTE format('SELECT setval(''%s'', %s, false)', seq_name, max_id);
    END LOOP;
END $$;

And there you have it! With this handy script, you can update auto-increment sequences after restoring your PostgreSQL database.