Postgres Generates Duplicate IDs After a Manual Import Leaves the Sequence Behind

Postgres inserts into an auto-increment column fail with duplicate key value violates unique constraint when the table’s sequence is behind the actual data.

What the error means

A typical failure looks like this:

sqlCopy
ERROR:  duplicate key value violates unique constraint "users_pkey"
DETAIL:  Key (id)=(42) already exists.

The table usually has a primary key or unique index on id, and inserts are supposed to get their id from a sequence owned by the column. If that sequence still thinks the next value is 42, but a row with id = 42 already exists, the insert fails immediately.

This is not a random uniqueness problem. It is a mismatch between:

• the rows already stored in the table, and
• the current state of the sequence that generates new IDs.

Postgres does not automatically compare those two values on every insert. It trusts the sequence.

How SERIAL and identity columns generate IDs

In Postgres, auto-incrementing columns are implemented with sequences.

A SERIAL column is shorthand for three separate objects:

• an integer column
• a sequence
• a default expression that calls nextval(...)

For example:

sqlCopy
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email text NOT NULL
);

That is roughly equivalent to:

sqlCopy
CREATE SEQUENCE users_id_seq;

CREATE TABLE users (
  id integer NOT NULL DEFAULT nextval('users_id_seq'),
  email text NOT NULL
);

ALTER SEQUENCE users_id_seq OWNED BY users.id;

Identity columns work the same way conceptually:

sqlCopy
CREATE TABLE users (
  id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  email text NOT NULL
);

The syntax is different, but the mechanism still depends on a sequence behind the column.

When you insert a row without specifying id, Postgres evaluates nextval(...) and uses the returned number. The sequence advances independently of the table contents.

Why imports and manual inserts leave the sequence behind

A sequence only moves forward when something calls nextval(...). It does not scan the table to find the largest existing id.

That creates a common failure mode after:

• pg_restore from a dump that omitted sequence state
• a bulk COPY import that included explicit id values
• manual inserts that specified id
• data migrations that inserted rows out of band
• loading test fixtures into a live or reused table

Example:

sqlCopy
INSERT INTO users (id, email) VALUES
  (100, 'a@example.com'),
  (101, 'b@example.com'),
  (102, 'c@example.com');

If the sequence still starts at 1, the next automatic insert tries 1, then 2, and so on. If rows with those IDs already exist, you get duplicate key value violates unique constraint.

This can also happen after deletes. If rows with high IDs exist and the sequence was manually reset to a lower value, the same collision appears later.

Inspect the table and the sequence state

First, identify the column and the sequence that backs it.

For a SERIAL column, pg_get_serial_sequence returns the sequence name:

sqlCopy
SELECT pg_get_serial_sequence('users', 'id');

Example result:

textCopy
public.users_id_seq

For an identity column, you can still inspect the sequence through the catalog, but pg_get_serial_sequence often works as well if the column has an associated sequence.

Next, check the sequence’s current value. The exact columns available depend on Postgres version, but last_value is the important one:

sqlCopy
SELECT last_value, is_called
FROM public.users_id_seq;

A result like this means the sequence is behind:

textCopy
 last_value | is_called
------------+-----------
         42 | t

Then check the data itself:

sqlCopy
SELECT MAX(id) AS max_id
FROM users;

If MAX(id) is 128 and the sequence is still at 42, the next automatic insert will eventually collide long before reaching the real maximum.

To see what value the sequence will hand out next, combine both concepts. If is_called is true, last_value + increment_by is the next candidate. If is_called is false, last_value itself is the next candidate. In practice, you usually do not need to compute that manually if you are about to reset the sequence from the table contents.

Reset the sequence to match the table

The standard fix is to advance the sequence to at least the current maximum ID in the table.

Use setval with max(id):

sqlCopy
SELECT setval(
  pg_get_serial_sequence('users', 'id'),
  COALESCE((SELECT MAX(id) FROM users), 1),
  true
);

That tells Postgres:

• use the sequence associated with users.id
• set its current value to the table maximum
• mark it as already used so the next nextval(...) returns the next number

If users contains 128 as its highest id, the next generated value becomes 129.

For a table that might be empty, use a form that avoids NULL problems:

sqlCopy
SELECT setval(
  pg_get_serial_sequence('users', 'id'),
  COALESCE((SELECT MAX(id) FROM users), 1),
  true
);

If the table is empty, MAX(id) is NULL, so COALESCE(..., 1) seeds the sequence sensibly. Another common pattern is to set an empty table to 1 and allow the next insert to return 1.

If you prefer to force the next value exactly, you can also use the three-argument form carefully:

sqlCopy
SELECT setval('public.users_id_seq', 128, true);

That means the next call to nextval('public.users_id_seq') returns 129.

Important detail about setval

The third argument matters.

• true means the sequence has already been used at that value, so the next nextval advances past it.
• false means the next nextval returns exactly the value you passed.

For aligning a sequence to an existing table, true is usually what you want. If the table’s max ID is 128, setting true ensures the next inserted row gets 129, not 128 again.

A reusable repair query

If you need to repair one table with one auto-incrementing column, this query is enough:

sqlCopy
SELECT setval(
  pg_get_serial_sequence('users', 'id'),
  (SELECT COALESCE(MAX(id), 1) FROM users),
  true
);

To verify the result:

sqlCopy
SELECT last_value, is_called
FROM public.users_id_seq;

SELECT MAX(id) AS max_id
FROM users;

You should see last_value aligned with the table maximum, and the next generated insert should use the next integer after that.

Handling identity columns

Identity columns use a sequence too, but the sequence name can be generated by Postgres and is not always obvious from the table definition.

You can still inspect the sequence with catalog queries or pg_get_serial_sequence in many cases:

sqlCopy
SELECT pg_get_serial_sequence('users', 'id');

Then repair it the same way:

sqlCopy
SELECT setval(
  pg_get_serial_sequence('users', 'id'),
  (SELECT COALESCE(MAX(id), 1) FROM users),
  true
);

If you want to reset an identity column definition itself, ALTER TABLE ... ALTER COLUMN ... RESTART WITH exists:

sqlCopy
ALTER TABLE users ALTER COLUMN id RESTART WITH 129;

That changes the sequence start value, but it is usually less flexible than computing MAX(id) directly after a data import. If the table contents are already present, setval tied to MAX(id) is the safer operational fix.

Why TRUNCATE ... RESTART IDENTITY is different

If you are deleting all rows, TRUNCATE can reset sequences automatically:

sqlCopy
TRUNCATE TABLE users RESTART IDENTITY;

This is useful only when the table is being emptied at the same time.

It does not help when the table already contains imported rows. In that case, you need to move the sequence forward to the existing maximum, not restart from zero or one.

Find all sequences that need repair

The same problem can exist on multiple tables after a restore or import. To inspect every serial-backed column in a schema, query the catalogs.

This example lists table, column, and sequence names:

sqlCopy
SELECT
  ns.nspname AS schema_name,
  cls.relname AS table_name,
  att.attname AS column_name,
  pg_get_serial_sequence(format('%I.%I', ns.nspname, cls.relname), att.attname) AS sequence_name
FROM pg_attribute att
JOIN pg_class cls
  ON cls.oid = att.attrelid
JOIN pg_namespace ns
  ON ns.oid = cls.relnamespace
WHERE att.attnum > 0
  AND NOT att.attisdropped
  AND cls.relkind = 'r'
  AND pg_get_serial_sequence(format('%I.%I', ns.nspname, cls.relname), att.attname) IS NOT NULL
ORDER BY schema_name, table_name, column_name;

For each result, compare the sequence state to the table maximum and advance it if needed.

If you want a repair script for one schema, you can generate statements dynamically:

sqlCopy
SELECT format(
  'SELECT setval(%L, COALESCE((SELECT MAX(%I) FROM %I.%I), 1), true);',
  pg_get_serial_sequence(format('%I.%I', ns.nspname, cls.relname), att.attname),
  att.attname,
  ns.nspname,
  cls.relname
) AS repair_sql
FROM pg_attribute att
JOIN pg_class cls
  ON cls.oid = att.attrelid
JOIN pg_namespace ns
  ON ns.oid = cls.relnamespace
WHERE att.attnum > 0
  AND NOT att.attisdropped
  AND cls.relkind = 'r'
  AND pg_get_serial_sequence(format('%I.%I', ns.nspname, cls.relname), att.attname) IS NOT NULL;

That produces executable setval statements for each sequence-backed column.

Why simply inserting a bigger ID is not enough

A manual insert that uses a higher explicit id can make the problem worse if the sequence stays behind.

For example:

sqlCopy
INSERT INTO users (id, email) VALUES (500, 'new@example.com');

This creates a larger maximum in the table, but it does not move the sequence. If the sequence was still at 42, the next automatic insert still tries 43. The collision continues until the sequence eventually catches up, which can take a long time or fail immediately if values between the old sequence position and the table maximum already exist.

The sequence must be updated explicitly.

Preventing the problem after imports

If you are loading data into a table that already has a sequence-backed key, make sure the sequence is repaired as part of the import process.

A practical pattern is:

1. load the data
2. compute MAX(id)
3. call setval(...)
4. verify the next insert succeeds

For a psql session:

sqlCopy
BEGIN;

-- load or insert data here

SELECT setval(
  pg_get_serial_sequence('users', 'id'),
  (SELECT COALESCE(MAX(id), 1) FROM users),
  true
);

COMMIT;

If you are restoring from a dump, prefer a dump format and restore method that preserves sequence state, such as pg_dump custom format plus pg_restore, and still verify sequence ownership and values after any partial data load.

Practical takeaway

If duplicate key value violates unique constraint appears on an auto-increment column after an import or manual insert, the sequence is probably behind the table data.

Prefer repairing the sequence with setval(pg_get_serial_sequence(...), MAX(id), true) because it aligns the generator with the actual rows already present. Use TRUNCATE ... RESTART IDENTITY only when the table is being emptied. To keep the problem from coming back, run a sequence reset as part of any bulk import or restore that writes explicit IDs.