[postgresql] How to add an auto-incrementing primary key to an existing table, in PostgreSQL?

I have a table with existing data. Is there a way to add a primary key without deleting and re-creating the table?

ALTER TABLE test1 ADD COLUMN id SERIAL PRIMARY KEY;

This is all you need to:

1. Add the id column
2. Populate it with a sequence from 1 to count(*).
3. Set it as primary key / not null.

Credit is given to @resnyanskiy who gave this answer in a comment.

To use an identity column in v10,

ALTER TABLE test 
ADD COLUMN id { int | bigint | smallint}
GENERATED { BY DEFAULT | ALWAYS } AS IDENTITY PRIMARY KEY;

For an explanation of identity columns, see https://blog.2ndquadrant.com/postgresql-10-identity-columns/.

For the difference between GENERATED BY DEFAULT and GENERATED ALWAYS, see https://www.cybertec-postgresql.com/en/sequences-gains-and-pitfalls/.

For altering the sequence, see https://popsql.io/learn-sql/postgresql/how-to-alter-sequence-in-postgresql/.

I landed here because I was looking for something like that too. In my case, I was copying the data from a set of staging tables with many columns into one table while also assigning row ids to the target table. Here is a variant of the above approaches that I used. I added the serial column at the end of my target table. That way I don't have to have a placeholder for it in the Insert statement. Then a simple select * into the target table auto populated this column. Here are the two SQL statements that I used on PostgreSQL 9.6.4.

ALTER TABLE target ADD COLUMN some_column SERIAL;
INSERT INTO target SELECT * from source;