Postgres add identity results in ERROR START value (0) cannot be less than MINVALUE (1)

Tried to run this because I want the auto generated IDs to start at 0

alter table gender
alter column gender_id ADD GENERATED ALWAYS AS IDENTITY (START WITH 0 INCREMENT BY 1)

and got error: START value (0) cannot be less than MINVALUE (1)

I’m not good at PostgreSQL yet so not sure how to get it to increment starting at 0.

Answer

All Identity columns create a sequence in the backend. In your sequence_option of your identity column you have not provided the MINVALUE that’s why by default it is considering the MINVALUE as 1. So you have to explicitly define the MINVALUE like below:

ALTER TABLE gender
ALTER COLUMN gender_id ADD GENERATED ALWAYS AS IDENTITY (MINVALUE  0 START WITH 0 INCREMENT BY 1)