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

Question

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?

Answer 1

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.

Answer 2

Suppose you have a table patient previously created as

CREATE TABLE patient( patientid int, col1 int );

and a row inserted as

INSERT INTO patient VALUES(1,5);

Firstly create a sequence starting +1 iterated from the max value of ID and make it default for your column

CREATE SEQUENCE mySeq START WITH 2;
ALTER TABLE patient ALTER COLUMN patientid SET DEFAULT nextval('mySeq');

and convert your column to a primary key

ALTER TABLE patient ALTER COLUMN patientid SET NOT NULL;
ALTER TABLE patient ADD CONSTRAINT uk_patientid UNIQUE (patientid);

whenever you insert new rows such as

INSERT INTO patient(col1) VALUES(10);
INSERT INTO patient(col1) VALUES(15);

you'll observe that you sucessfully made your column as an identity column

SELECT * FROM patient

patientid  col1
---------  ----
1          5
2          10
3          15

Answer 3

You can use dynamic SQL to substitute the initial value of the sequence.

do $$
declare
id_max int4;
begin
select max(patientid)+1 into id_max from patient;
execute 'alter table patient alter patientid set not null,
                             alter patientid add generated by always as identity  (start with '||id_max||' )';
end;
$$;

Test it in db<>fiddle