CODEWITHSUNDEEP
postgresqlprimary-keyauto-increment
xRobot
xRobot

Reputation: 26567

How to add an auto-incrementing primary key to an existing table in PostgreSQL?

I have a PostgreSQL table with existing data.

How do I add an auto-incrementing primary key without deleting and re-creating the table?

Upvotes: 311

Views: 355281

Answers (7)

leonbloy
leonbloy

Reputation: 75906

Versions of PostgreSQL v10+

Suppose you have a table table_name, to which you want to add an auto-incrementing, primary-key id (surrogate) column. The recommended way is using the form GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ].

e.g. (ref)

ALTER TABLE table_name 
ADD COLUMN id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY;

or

ALTER TABLE table_name 
ADD COLUMN id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY;

Older versions of PostgreSQL

This is not recommended but continues to be supported as of PostgreSQL v16.

Note: The older SERIAL form was replaced with GENERATED ... AS IDENTITY in PostgreSQL v10 because SERIAL could cause problems, such as allowing an accidental override of the value and requiring more grants to allow inserts (PostgreSQL: serial vs identity).

The following form was used:

ALTER TABLE test1 ADD COLUMN id SERIAL PRIMARY KEY;

Internally this SERIAL is not preserved, it is expanded at parse time into a SEQUENCE and a SET DEFAULT nextval({sequence_name}) (more detailed discussion), saving you from explicitly typing those as was required in older versions, as outlined here:

Even Older Versions of PostgreSQL

In old versions of PostgreSQL (prior to 8.x?) you had to do all the dirty work:

ALTER TABLE test1 ADD COLUMN id INTEGER;
CREATE SEQUENCE test_id_seq OWNED BY test1.id;
ALTER TABLE test1 ALTER COLUMN id SET DEFAULT nextval('test_id_seq');
UPDATE test1 SET id = nextval('test_id_seq');

Upvotes: 564

Mark Shot
Mark Shot

Reputation: 329

I have Postgres 15.4

For example

  • table: 'post'.
  • column: 'id' primary column integer, without auto increment.

1 - Add GENERATED IDENTITY

ALTER TABLE post ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY

2 - Set sequence value of MAX(id) or 1 if there is no record

SELECT SETVAL(pg_get_serial_sequence(post, 'id'), COALESCE((SELECT MAX(id) FROM post) + 1, 1))

Read about:

  1. Parameter GENERATED BY DEFAULT and ALWAYS
  2. Sequence in Postgres

Upvotes: 3

Павел К.
Павел К.

Reputation: 631

I understand it's been a lot of years, but this may help other users. Try to use first query:

ALTER TABLE dbo."Users"
ALTER COLUMN "UserID" 
ADD GENERATED BY DEFAULT AS IDENTITY (MINVALUE 1 START WITH 1 INCREMENT BY 1)

second query for update current value:

SELECT setval('"Users_UserID_seq"', (SELECT max("UserID")+1 FROM dbo."Users"), false)

where "Users" - table, "UserID" - primary key

Upvotes: 3

yaseer
yaseer

Reputation: 31

ALTER TABLE test1 ADD id int8 NOT NULL GENERATED ALWAYS AS IDENTITY;

Upvotes: 1

jhoanna
jhoanna

Reputation: 1857

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/.

Upvotes: 23

Dean Sha
Dean Sha

Reputation: 845

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;

Upvotes: 7

Synesso
Synesso

Reputation: 38978

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.

Upvotes: 79

Related Questions