Your PostgreSQL database import went fine, but now your auto-increment columns aren’t working properly. New records are throwing errors or starting from 1 instead of continuing where they left off. This breaks your application and can cause duplicate key violations that bring everything to a halt.
Step-by-Step Fixes
Step 1: Check Current Sequence Values
First, let’s see what your sequences think the next value should be. Open your PostgreSQL client (pgAdmin, psql, or DBeaver) and run this query:
“`sql
SELECT schemaname, sequencename, last_value
FROM pg_sequences
WHERE schemaname = ‘public’;
“`
This shows all sequences in your public schema. Look for sequences that match your table names – they usually follow the pattern `tablename_id_seq`. If the `last_value` is null or lower than your actual data, that’s your problem.
Step 2: Find Your Maximum ID Values
Now check what the highest ID actually is in each table. Replace `your_table` with your actual table name:
“`sql
SELECT MAX(id) FROM your_table;
“`
Run this for each table with auto-increment issues. Write down these numbers – you’ll need them in the next step.
Step 3: Reset Sequences to Correct Values
Time to fix those sequences. For each problematic sequence, run:
“`sql
SELECT setval(‘your_table_id_seq’, (SELECT MAX(id) FROM your_table));
“`
This sets the sequence to match your highest existing ID. PostgreSQL will use the next number for new inserts. If you get an error about the sequence not existing, check the exact sequence name using the query from Step 1.
Step 4: Verify the Fix
Test that it worked by inserting a dummy record:
“`sql
INSERT INTO your_table (name) VALUES (‘Test Record’);
SELECT id FROM your_table ORDER BY id DESC LIMIT 1;
“`
The new record should have an ID one higher than your previous maximum. Delete the test record once confirmed.
Step 5: Fix All Tables at Once
If you have many tables, this script resets all sequences automatically:
“`sql
DO $$
DECLARE
r RECORD;
BEGIN
FOR r IN
SELECT tablename, sequencename
FROM pg_sequences
WHERE schemaname = ‘public’
LOOP
EXECUTE format(‘SELECT setval(”%s”, COALESCE((SELECT MAX(id) FROM %s), 1), true);’,
r.sequencename,
split_part(r.sequencename, ‘_id_seq’, 1));
END LOOP;
END $$;
“`
This loops through all sequences and resets them based on actual table data.
Likely Causes
Cause #1: Import Method Didn’t Include Sequences
Most database dumps using `pg_dump` with the `–data-only` flag skip sequence values. This is ideal for moving data between servers but not recommended when you need working auto-increments. Check your import command – if you see `–data-only` or similar flags, that’s why sequences weren’t updated.
To check: Look at your dump file. Search for “SEQUENCE” – if you don’t find any `SELECT pg_catalog.setval` statements, your sequences weren’t exported.
Fix: Next time, use `pg_dump` without restrictive flags, or explicitly include `–sequence-data` when doing data-only dumps.
Cause #2: Different PostgreSQL Versions
Moving data between PostgreSQL versions (like 12 to 15) can cause sequence issues. Older versions handle sequences differently than newer ones. Version 10 introduced major changes to sequence handling that affect imports.
To check: Run `SELECT version();` on both source and destination databases. Major version differences often cause this problem.
Fix: Use the reset queries above, then update your migration process. Consider using `pg_upgrade` for major version changes, best used in production environments where data integrity matters.
Cause #3: Manual ID Insertions
If your application or import process manually inserted IDs instead of letting PostgreSQL generate them, sequences don’t update automatically. This commonly happens with data migrations from other database systems.
To check: Look at your import SQL. If you see `INSERT INTO table (id, name) VALUES (1, ‘data’)` with explicit ID values, that’s the issue.
Fix: After importing with manual IDs, always reset sequences using the queries above. For future imports, remove ID columns from INSERT statements when possible.
When to Call Expert Help
Contact a database administrator when you see these warning signs:
- Multiple “duplicate key value violates unique constraint” errors across different tables
- Sequences exist but tables are missing or corrupted
- Production data is affected and you’re not comfortable running SQL commands
- You need to merge data from multiple sources with conflicting IDs
Database experts can write custom migration scripts that handle complex scenarios. They’ll also set up proper backup strategies to prevent future issues. In 2025, most PostgreSQL hosting providers offer support tickets – use them before attempting fixes on production data.
Copy-Paste Prompt for AI Help
If you need more specific help, copy this prompt:
“I’m having PostgreSQL auto-increment issues after importing a database. My sequences aren’t working correctly. Here’s what I know:
- PostgreSQL version: [your version]
- Import method used: [pg_dump, SQL file, etc.]
- Error message: [paste exact error]
- Table name: [your table]
- Current max ID in table: [number]
- Current sequence value: [number]
Please provide specific SQL commands to fix my sequences and prevent this in future imports.”
Remember to backup your database before running any fixes. Sequence problems are usually simple to solve once you understand what went wrong. Take it step by step, and your auto-increment columns will be working again in minutes.