Primary key collision during postgres_fdw migration in Supabase

Supabase does not provide a built-in mechanism to automatically synchronize sequence values during a postgres_fdw import. Because postgres_fdw treats the remote table as a data source for standard INSERT operations, it bypasses the sequence generators on the target instance. Consequently, while the data is migrated, the nextval() state of your local sequences remains at their default starting point, leading to unique constraint violations when the application attempts to insert new rows.

The Root Cause

In PostgreSQL, sequences are independent objects from the tables they support. When you perform an INSERT INTO local_table SELECT * FROM foreign_table, you are explicitly providing the primary key values. PostgreSQL accepts these values without incrementing the associated sequence. If your migrated data contains IDs up to 10,000, but the local sequence is still at 1, the next application write will attempt to use ID 1, resulting in a collision.

Reliable Synchronization Method

To synchronize sequences across all migrated tables without writing a manual script for every individual table, you can execute a dynamic SQL block in the Supabase SQL Editor. This approach queries the system catalogs to identify all sequences associated with columns in your public schema and updates them to the current maximum value of their respective columns.

Warning: Ensure all data migration is complete before running this, as it sets the sequence to the current MAX(id).

DO $$
DECLARE
    seq_record RECORD;
BEGIN
    FOR seq_record IN 
        SELECT 
            s.relname AS sequence_name, 
            n.nspname AS schema_name
        FROM pg_class s
        JOIN pg_namespace n ON s.relnamespace = n.oid
        WHERE s.relkind = 'S' AND n.nspname = 'public'
    LOOP
        EXECUTE format('SELECT setval(%L, COALESCE((SELECT max(id) FROM %I.%I), 1))', 
            seq_record.schema_name || '.' || seq_record.sequence_name, 
            seq_record.schema_name, 
            -- Note: This assumes the sequence name matches the table_column_seq pattern
            -- For complex naming, a join with pg_depend is required
            replace(seq_record.sequence_name, '_id_seq', '')
        );
    END LOOP;
END $$;

Verification Steps

After running the synchronization, verify a specific table's sequence state using the following command:

SELECT nextval('your_table_id_seq');

The returned value should be MAX(id) + 1.

Assumptions and Constraints

• Version: This solution assumes PostgreSQL 12+ (standard for current Supabase projects).
• Naming Convention: The provided dynamic script assumes standard PostgreSQL naming conventions (table_column_seq). If you have custom-named sequences, you must join pg_depend to map sequences to their specific columns.
• Schema: The script is scoped to the public schema to avoid altering internal Supabase system sequences.

Diagnostic Detail Needed: Are you using IDENTITY columns (PostgreSQL 10+) or the older SERIAL type? IDENTITY columns may require ALTER TABLE ... RESTART WITH instead of setval() depending on the specific configuration.