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

Question

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 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?

Answer 1

You don't need to know the sequence name behind the identity column to modify the value. It can be done with

ALTER TABLE tab ALTER col RESTART WITH 1000;