How can I change current value for identity type primary key column in table in PostgreSQL database?

Question

I had an application and PostgreSQL database with EF Core ORM. There was a table Cars and Id column had a sequence, where I could change current value for next record. Now I created a new app and moved records from old table to new table in new database. First record has an Id=12000 for example.

New records started from 1 and when it reached record with Id 12000 I started get following error:

Npgsql.PostgresException (0x80004005): 23505: duplicate key value violates unique constraint "PK_Cars"

In new table Id column not using sequence anymore, it has an Identity type and I can not change current value in pgAdmin. How can I make my Id column change current id value?

Answer 1

First, find out the actual maximum in the table:

SELECT max(id) FROM tab;

  max 
════════
 123000
(1 row)

Then, set the underlying sequence to a higher value:

ALTER TABLE tab ALTER id RESTART 200000;