How can i change existing column as Identity in PostgreSQL 11.1
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
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.