Re: [NOVICE] alter existing table column with primary key to auto-increment - Mailing list pgsql-novice

Rounak Jain <rounakjainis@gmail.com> writes:
> I am just starting with psql.
> I have created surrogate id keys in some tables.
> They are primary keys.
> I want to add auto-increment to them.

You want to read the definition of serial types here:

https://www.postgresql.org/docs/current/static/datatype-numeric.html#DATATYPE-SERIAL

If you already have the column, you can make the sequence object, add the
default expression (ALTER TABLE ... ALTER COLUMN ... SET DEFAULT ...
I think is the right syntax) and do the ALTER OWNED BY if you'd like the
sequence to go away if the column is dropped.

If there's data in the column already, you probably also need to tweak the
current value of the sequence.

            regards, tom lane