---
title: "Postgres Generates Duplicate IDs After a Manual Import Leaves the Sequence Behind"
description: "Postgres inserts collide when an imported table’s sequence is lower than existing rows; reset the sequence to match the data."
url: "/postgres-generates-duplicate-ids-after-a-manual-import-leaves-the-sequence-behind"
canonical_url: "https://bfzli.com/postgres-generates-duplicate-ids-after-a-manual-import-leaves-the-sequence-behind"
source_url: "https://bfzli.com/postgres-generates-duplicate-ids-after-a-manual-import-leaves-the-sequence-behind.md"
type: "article"
updated: "2026-09-22"
date: "2026-09-22"
tags: ["postgres", "sql", "sequence", "migrations"]
---

> Markdown copy of https://bfzli.com/postgres-generates-duplicate-ids-after-a-manual-import-leaves-the-sequence-behind. Append `.md` to any page path on bfzli.com for its markdown twin. Full index: https://bfzli.com/llms.txt

# 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:

```sql
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:

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

That is roughly equivalent to:

```sql
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:

```sql
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:

```sql
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:

```sql
SELECT pg_get_serial_sequence('users', 'id');
```

Example result:

```text
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:

```sql
SELECT last_value, is_called
FROM public.users_id_seq;
```

A result like this means the sequence is behind:

```text
 last_value | is_called
------------+-----------
         42 | t
```

Then check the data itself:

```sql
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)`:

```sql
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:

```sql
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:

```sql
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:

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

To verify the result:

```sql
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:

```sql
SELECT pg_get_serial_sequence('users', 'id');
```

Then repair it the same way:

```sql
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:

```sql
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:

```sql
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:

```sql
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:

```sql
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:

```sql
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:

```sql
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.