How to reset Postgres' primary key sequence when it falls out of sync?

Procedure for reset

Before you dive into resetting the sequence, let's understand the best practices and step-by-step strategy for a safe and effective reset.

1. Backup your data

You're not a daredevil, right? So start with a backup. Use pg_dump as your safety net. When (not if) something goes wrong, you'll thank yourself.

pg_dump your_database > your_backup.sql


// Note: No capes. Or in this case, no resets without backups!

2. Prevent table changes during reset

Lock your table. Like a careful mystic sealing exorcized spirits in a haunted manor while they recite the incantation, preventing other transactions from meddling is a good idea.

BEGIN;
LOCK TABLE your_table IN EXCLUSIVE MODE;


// "Exorcizo te, omnis spiritus duplicatus!"

3. Adjust the sequence

Now the real deal: invoke setval, the secret charm of sequence reset.

SELECT setval(
  'your_table_id_seq',
  COALESCE((SELECT MAX(id) FROM your_table), 1),
  false
);
COMMIT;


// SHAZAM! Sequence reset!

4. Kickoff for empty tables

For empty tables, we use COALESCE to prevent the sequence from scoffing and returning a NULL.

SELECT setval('your_table_id_seq', COALESCE((SELECT MAX(id)+1 FROM your_table), 1));


// Like replying to an RSVP for a party-of-none

5. Keeping the ghosts — ID Gaps

If you are one to cherish nostalgia and want to keep the ID gaps, which might be due to deleted records over time or a trip to Bermuda triangle (database edition), you can set the third argument of setval to true:

SELECT setval('your_table_id_seq', (SELECT MAX(id)+1 FROM your_table), true);


6. Operating on all sequences

Need to operate on all sequences in your database due to a sudden outbreak of sequence disorder? Fear not! Call Ghostbusters!.. I mean.. use information_schema.columns.

DO $$
DECLARE
  record RECORD;
BEGIN
  FOR record IN 
    SELECT column_default like 'nextval%' as is_serial, 
           table_name, 
           column_name 
    FROM information_schema.columns 
    WHERE table_schema = 'public'
  LOOP
    IF record.is_serial THEN
      EXECUTE 'SELECT setval(' ||
        pg_get_serial_sequence(quote_ident(record.table_name), record.column_name) ||
        ', (SELECT COALESCE(MAX('|| record.column_name ||')+1, 1) FROM '|| quote_ident(record.table_name) || '), false)';
    END IF;
  END LOOP;
END$$;


// DO finish that "one thing" on your weekend to-do list

Beyond reset: Advanced sequence handling

Now that we've reset our sequence, let's divulge some savvy secrets for advanced handling of sequences. Buckle up, we're going for a roller-coaster ride!

1. Sequences in multi-table scenarios

For multi-table or inheritance scenarios, use pg_get_serial_sequence to make sure you get ninja-level accuracy.

SELECT pg_get_serial_sequence('child_table', 'id');


// Sequence ninja, activate!

2. Command the sequence; Own it

When resetting a sequence, it's absolutely critical to pull out the wand of ALTER SEQUENCE ... OWNED BY. This helps to ensure the sequence is owned by the right column.

ALTER SEQUENCE your_table_id_seq OWNED BY your_table.id;


// Remember, "ownership" is path to enlightenment!

3. Building a custom reset charm

Ever dreamed of having your own spell? Now you can! If your DB suffers frequent sync issues, a custom function like reset_sequence can be your Patronus!

CREATE OR REPLACE FUNCTION reset_sequence(tablename TEXT, columnname TEXT) RETURNS VOID AS $$
DECLARE
  seq_name TEXT;
  seq_val BIGINT;
BEGIN
  seq_name := (SELECT pg_get_serial_sequence(tablename, columnname));
  EXECUTE 'SELECT COALESCE(MAX(' || columnname || ') + 1, 1) FROM ' || tablename INTO seq_val;
  EXECUTE 'SELECT setval(''' || seq_name || ''', ' || seq_val || ', false)';
END;
$$ LANGUAGE plpgsql;


// From now on, you can call yourself "The Reset Wizard".

4. Post-restore sequence adjustments

Heavy import or unloading the bits from a backup? Remember to give a makeover to your sequences to avoid key conflicts:

SELECT setval('your_table_id_seq', (SELECT MAX(id)+1 FROM your_table), false);


// Freshen up post-travel, sequences!