Postgres - fix sequences after importing data

README.md

This script is designed to help reset all sequences in a PostgreSQL database to their correct values based on existing data in the tables.

Purpose

In PostgreSQL, sequences are used to auto-increment integer columns in tables. These sequences need to have their "last value" accurately set to avoid conflicts when inserting new rows into the table. This can become an issue, for example, when data is imported into the database and the sequences are not updated accordingly, leading to unique constraint

How it Works

The script works by iterating over each table in the current schema of the PostgreSQL database. For each table, it dynamically looks up the associated sequence and the column that the sequence is used for. It then finds the maximum value in that column and sets the "last value" of the sequence to this maximum value.

Safety Precautions

Always back up your database before running scripts that modify data or database objects.

Review the output of the dry run version of the script first to ensure it's going to do what you expect. The dry run version does not actually change any data; it simply outputs the SQL statements that would be executed.

fix_sequences.sql

DO $$
DECLARE 
   rec         record;
   sequence    text;
   max_id      bigint;
BEGIN
   -- Iterate over each table in the current schema
   FOR rec IN (select distinct on (table_name) t.relname as table_name, a.attname as column_name, seq.relname as sequence_name
               FROM pg_class t
               JOIN pg_depend d ON d.refobjid = t.oid 
               JOIN pg_class seq ON d.objid = seq.oid
               JOIN pg_attribute a ON a.attnum = d.refobjsubid, pg_namespace n
               WHERE t.relkind = 'r' AND a.attnum > 0 AND d.deptype = 'a' AND t.relnamespace = n.oid AND n.nspname = current_schema()
               and a.attname = 'id'
              )
   LOOP
      sequence := rec.sequence_name;
      -- Get the maximum id value from the table and print out the setval statement
      BEGIN
         EXECUTE format('SELECT max(%I) FROM %I', rec.column_name, rec.table_name) INTO max_id;
         IF max_id IS NOT NULL then
            RAISE NOTICE 'executing: SELECT setval(''%'', %)', sequence, max_id;
	        EXECUTE format('SELECT setval(''%I'', %s)', sequence, max_id);
         END IF;
      EXCEPTION WHEN undefined_table THEN
         -- Do nothing when the sequence does not exist
         CONTINUE;
      END;
   END LOOP;
END;
$$;