Reset identity column with last value of table's identity in postgres

Question

I would like to changes my existing column as Auto Identity in a Postgres Database.
I used below script to set auto identity column and set default start with 1.
But here i would like to reset identity column value to table's last value.
Let me know if you have any solutions.

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

Here I'm setting up something like this:

 ALTER TABLE Patient 
   ALTER COLUMN PatientId RESTART WITH (select Max(patientId) + 1 from patient);

Ia

Answer 1

An identity columns is also backed by a sequence, and pg_get_serial_sequence() will return that sequence name. That can be used together with setval().

After running the add generated always part you can adjust the sequence using this statement:

SELECT setval(pg_get_serial_sequence('patient', 'patientid'), 
                                      (select max(patientid) from patient));

Faltboat

That solution i already know. but i don't want to use serial.here i would like to use identity as describe in a question. – Ia

@NayanRudani: as I wrote in my answer: an identity column also uses a sequence. The above code will work for identity columns just as well. – Faltboat

I got it. Thanks – Ia