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?

Please, always disclose your Postgres version (SELECT version();) — Erwin Brandstetter
– Erwin Brandstetter, Commented Sep 11, 2021 at 22:38

Erwin Brandstetter · Accepted Answer · 2021-09-11 22:53:26Z

3

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

edited Sep 11, 2021 at 22:53

answered Sep 11, 2021 at 22:47

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

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

Comments

Gordon Linoff · Accepted Answer · 2021-09-11 21:31:31Z

1

You can simply add an identity column:

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

Here is a db<>fiddle.

answered Sep 11, 2021 at 21:31

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Collectives™ on Stack Overflow

Postgres - add sequential column to existing data

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related