Sample Header Ad - 728x90

PostgreSQL: How to update a identity column to fix the error "duplicate key value violates unique constraint"

1 vote
1 answer
774 views
I have a table with the following structure:
CREATE TABLE categories (
  id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name varchar NOT NULL
);
When I inserted some records with an explicit *id*:
INSERT INTO categories (id, name) VALUES (1, 'Children Bicycles'), (2, 'Comfort Bicycles'), (3, 'Cruisers Bicycles');
And then tried to insert a new record without specifying an *id*
INSERT INTO categories (name) VALUES ('Eletric Bicyles');
I got the error duplicate key value violates unique constraint "categories_pkey" I've already found this question [postgresql duplicate key violates unique constraint](https://stackoverflow.com/questions/4448340/postgresql-duplicate-key-violates-unique-constraint) where it says that happens due the sequence is outdated and the solution is to set the next value of the sequence to the next MAX value of the primary key plus one, but since I declare the primary key as an IDENTITY I'm not able to use the answer of that question as a solution for my case. So, my question is what should I do in order to set the next value of an IDENTITY in PostgreSQL?
Asked by Jaime Suncin (111 rep)
Oct 21, 2023, 04:51 AM
Last activity: Oct 21, 2023, 05:59 AM