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. — user330315
– user330315, Commented Aug 27, 2020 at 12:25
Oh, thanks for warning. What's the best approach to create auto incremented PKs? — Süleyman Sümertaş
– Süleyman Sümertaş, Commented Aug 27, 2020 at 12:27

user330315 · Accepted Answer · 2020-08-27 14:14:53Z

1

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;

answered Aug 27, 2020 at 14:14

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgresql add primary key as serial using contraint

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related