Postgres - add sequential column to existing data

Question

I have an existing table that currently doesn't have an id column and a lot of duplicate rows on what should be a unique pair - it's messy. Example:

 fips  | customer_id
-------+------------
 17043 |        2085
 17043 |        2085
 42091 |        4426
 42091 |        4426

customer_id/fips should be unique, but the current code and schema don't enforce that. There also isn't an id column, so I have no unique way to reference a single row.

I'd like to add an id column and assign sequential integers so I can have a unique primary key. How can I go about that?

Answer 1

Postgres 10 added IDENTITY columns (as demonstrated in Gordon's answer).
In Postgres 9.6 (or any version) you can use use a serial column instead.

Either way, make it the PRIMARY KEY in the same command. That's cheaper for big tables:

ALTER TABLE tbl ADD COLUMN tbl_id serial PRIMARY KEY;

Or:

ALTER TABLE tbl ADD COLUMN tbl_id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY;

db<>fiddle here

IDENTITY columns are not PRIMARY KEY automatically. Postgres allows multiple IDENTITY columns for the same table (even if that's rarely useful).

See:

• Auto increment table column

Or you clean up the mess to make (fips, customer_id) unique. Then that can be your PK. See:

• How to delete duplicate rows without unique identifier

Answer 2

You can simply add an identity column:

alter table t add column id int generated always as identity;

Here is a db<>fiddle.