Search code examples
postgresqlidentityddl

How can I change an existing column to Identity in PostgreSQL 11.1

I would like to change my existing column to Auto Identity in a Postgres Database.

I don't want to use Postgres SEQUENCE. I would like to use GENERATED ALWAYS AS IDENTITY.

I tried the script below but it didn't work.

ALTER TABLE public.patient ALTER COLUMN patientid Type int4 
USING patientid::int4 GENERATED ALWAYS AS IDENTITY;

What should I do?

Solution

  • Following the documentation

    ALTER TABLE patient 
    ALTER patientid SET NOT NULL,  -- optional
    ALTER patientid ADD GENERATED ALWAYS AS IDENTITY 
        (START WITH 2);  -- optional

    Add NOT NULL constraint if the column does not have the constraint yet. The optional clause START WITH start changes the recorded start value of the sequence.

    Test it in DB<>Fiddle.