If you're working with PostgreSQL and get a unique constraint violation, the first question you have to ask yourself is whether you've executed any SQL queries outside of your application.
Possible error messages
Express app using prisma as ORM:
{"message":"Failed to create category","error":"\nInvalid 'prisma.category.create()' invocation:\n\n\nUnique constraint failed on the fields: (id)"}
Or directly in PostgreSQL:
ERROR: duplicate key violates unique constraint
Problem
You have a table with existing rows of data.
You're trying to add 1 or more additional rows.
PostgreSQL thinks the next available row has id=#, let's say id=4.
However, you already have a row with id=4. Perhaps you earlier manually updated the database via psql or the PgAdmin UI or some batch job ran, but now you're trying to add data through an application you're building.
Therefore, conflict!
Solution
Update the sequence. The sequence is a number generator, providing unique identifiers for your table rows.
Begin by starting a shell with psql for your database and then let's update the sequence for the affected table.
First, figure out what the highest id currently is, so we know where the sequence should be starting.
Example for a Category table:
SELECT MAX(id) FROM "Category";
If we get a 4 from this, we'll want to start the sequence at 5:
ALTER SEQUENCE "Category_id_seq" RESTART WITH 5;
Next time you're inserting data, it will start at row id=5.
Notes
- This is assuming you're auto-incrementing your ids. Example for a prisma schema:
model Category {
id Int @id @default(autoincrement())
}- You'll want to do all your future updates via the application you're building.