PostgreSQL: '23505: duplicate key value violates unique constraint' after seeding rows

23505: duplicate key value violates unique constraint "PK_Items" on a plain insert, right after rows were seeded or imported, means the table's identity sequence is behind: the rows went in with explicit ids, and the sequence still hands out 1. Move it past the highest id with setval and the next insert succeeds.

The fix is one SQL statement per table. The lasting fix is to seed without writing ids yourself, or to end every seed script and import with that same statement.

The error

Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while saving the entity changes. See the inner exception for details.
 ---> Npgsql.PostgresException (0x80004005): 23505: duplicate key value violates unique constraint "PK_Items"

DETAIL: Detail redacted as it may contain sensitive data. Specify 'Include Error Detail' in the connection string to include this information.

With Include Error Detail=true in the connection string (development only), the detail line names the key: DETAIL: Key ("Id")=(1) already exists.

Why it happens

EF Core creates the key as "Id" integer GENERATED BY DEFAULT AS IDENTITY. "By default" means PostgreSQL uses the sequence only when the insert leaves the column out; an explicit value is accepted as it is, and the sequence is not told. Here a seed script inserted ids 1, 2 and 3 directly. Afterwards the sequence still read last_value 1, is_called f, so EF Core's next insert, which omits the id, was given 1 and hit the primary key. A COPY import with an id column does the same, and so does app code that sets Id itself.

Each failed insert still uses up a sequence value (after two failures the sequence stood at 2), so the error walks through the seeded ids and can look intermittent. Seed data from EF Core's HasData was not the culprit in the versions checked: Npgsql's provider 10.0.3 and 8.0.11 both end the seed with a setval call, and 10.0.3 does it in migrations and in EnsureCreated. Paste your migration script into the EF Core migration SQL explainer to see that line.

The fix

SELECT setval(pg_get_serial_sequence('"Items"', 'Id'), (SELECT MAX("Id") FROM "Items"));

It returned 3, and the app's next insert got id 4. The table name keeps its double quotes inside the string because EF Core created it in mixed case. Run it for every table that received explicit ids.

To stop it recurring, let the database number seed rows. EF Core's UseSeeding option adds them through the context, without ids:

builder.Services.AddDbContext<ShopSeedDb>(o => o
    .UseNpgsql(Cs + "e5s")
    .UseSeeding((context, _) =>
    {
        var items = context.Set<Item>();
        if (!items.Any())
        {
            items.AddRange(
                new Item { Name = "Stethoscope" },
                new Item { Name = "Thermometer" },
                new Item { Name = "Blood pressure cuff" });
            context.SaveChanges();
        }
    })

Cs is the lab's connection string without the database name. The lab registers the same body in UseAsyncSeeding. It ran on both EnsureCreated and dotnet ef database update, and each time the sequence itself numbered the rows, so it cannot fall behind. For a hand-written seed script, leave the id column out or end it with the setval above.

How it was reproduced

A minimal API from dotnet new web with Microsoft.EntityFrameworkCore 10.0.12 and Npgsql.EntityFrameworkCore.PostgreSQL 10.0.3, an Items table created by EnsureCreated, and a psql script inserting three rows with ids. A POST that added an item without an id returned 500 with the error above. The HasData variant, through both migrations and EnsureCreated, inserted without error. .NET SDK 10.0.401 on Windows, PostgreSQL 18.4 in the postgres:18 Docker image.

Frequently asked

How do I reset an identity sequence in PostgreSQL after inserting rows with ids?

Run SELECT setval(pg_get_serial_sequence('"Items"', 'Id'), (SELECT MAX("Id") FROM "Items")); with your own table and column names. The next insert without an id then gets the maximum plus one.

Why does PostgreSQL say duplicate key value violates unique constraint on the primary key?

The id the insert used already exists. On a GENERATED BY DEFAULT identity column this usually means earlier rows were inserted with explicit ids, by a seed script, an import or app code, so the sequence was never advanced and hands out an id that is taken.

Does EF Core HasData break the PostgreSQL identity sequence?

Not in the Npgsql versions checked here, 10.0.3 and 8.0.11. The provider follows the HasData inserts with a setval that moves the sequence past the seeded ids. Rows added by other means, such as a seed script or an import, still need the setval by hand.

More decoded errors in the Fixes category. If your rows arrived by bcp and COPY as in the SQL Server to PostgreSQL migration plan, run the setval for each identity table once the copy is done.