Imported data, duplicate key value violates unique constraint

Question

I am migrating data from MSSQL. I created the database in PostgreSQL via npgsql generated migration. I moved the data across and now when the code tries to insert a value I am getting 'duplicate key value violates unique constraint'

The npgsql tries to insert a column with Id 1..how ever the table already has Id over a thousand.

Npgsql.EntityFrameworkCore.PostgreSQL is 2.2.3 (latest)

In my context builder, I have

 modelBuilder.ForNpgsqlUseIdentityColumns();

In which direction should I dig to resolve such an issue?

The code runs fine if the database is empty and doesn't have any imported data

Thank you

Answer 1

The values inserted during the migration contained the primary key value, so the sequence behind the column wasn't incremented and is kept at 1. A normal insert - without specifying the PK value - calls the sequence, get the 1, which already exists in the table.

To fix it, you can bump the sequence to the current max value.

SELECT setval(
        pg_get_serial_sequence('myschema.mytable','mycolumn'), 
        max(mycolumn)) 
FROM myschema.mytable;

If you already know the sequence name, you can shorten it to

SELECT setval('my_sequence_name', max(mycolumn)) 
FROM myschema.mytable;