Postgresql add primary key as serial using contraint

Question

I have a table which has an Id column as int but not mentioned as a primary key. Now I want to add primary key constraint as serial. I've seen this question Postgresql 9.4, Make existing primary key as SERIAL however this is using alter table statement while my intention is doing that with constraint. Is there any way to do that like below?

constraint "PK_tablename" serial primary key ("Id")

Note that serial is discouraged when using a current Postgres version in favor of `identity` columns. — , Aug 27 '20 at 12:25
Oh, thanks for warning. What's the best approach to create auto incremented PKs? — Süleyman Sümertaş, Aug 27 '20 at 12:27

score 1 · Accepted Answer · answered Aug 27 '20 at 14:14

You can add the constraint and the identity property in a single statement:

alter table the_table
    add constraint pk_the_table primary key (id),
    alter id add generated always as identity;

As you most probably have already data in your table, you need to adjust the sequence used by the identity clause:

select setval(pg_get_serial_sequence('the_table', 'id'), max(id))
from the_table;

Postgresql add primary key as serial using contraint

1 Answers1