I see that the question is already marked as solved, however, as pointed out in the comments, it is missing the case where there are existing records that already have some values set in the relevant column.
I'll list some other cases as well.
I'm doing this for a table called products
which is present in the public
schema. The auto-increment is being done for the id
column of this table.
1. For a non-existing column
-- auto-increment constraint for a new column
ALTER TABLE public.products
ADD COLUMN id SERIAL PRIMARY KEY;
2. For an existing column that got no values in the table
-- create sequence
CREATE SEQUENCE public_products_id_seq OWNED BY public.products.id;
-- use sequence for the target column
ALTER TABLE public.products ALTER COLUMN id SET DEFAULT nextval('public_products_id_seq');
3. For an existing column that already got some values in the table
-- create sequence
CREATE SEQUENCE public_products_id_seq OWNED BY public.products.id;
-- set the current value of the sequence to the max value from that column
-- (id column in this scenario)
SELECT SETVAL('public_products_id_seq', (select max(id) from public.products), false)
-- use sequence for the target column
ALTER TABLE public.products ALTER COLUMN id SET DEFAULT nextval('public_products_id_seq');
Documentation references for CREATE SEQUENCE and SETVAL