314

I have a PostgreSQL table with existing data.

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

Improve this question

7 Answers 7

Reset to default
571

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');
Improve this answer
Sign up to request clarification or add additional context in comments.

18 Comments

I am using ORACLE, so sharing it might be useful for ORACLE guys In ORACLE : ALTER TABLE TEST1 ADD ID NUMBER; UPDATE TEST1 SET ID = TEST1_SEQ.NEXTVAL; ALTER TABLE TEST1 ADD PRIMARY KEY(ID); create a Sequence TEST1_SEQ before executing UPDATE statement
Further to @resnyanskiy's comment, this will work even when there's data in the table. IDs are populated and not null constraint is set. The entire answer can be replaced by the line in that comment.
@EricWang Thanks, Eric, you're right - I believe that this didn't work some versions (years) ago, but I'm not sure. Turned the answer into community-wiki.
When using the ALTER TABLE <table> ADD COLUMN id SERIAL PRIMARY KEY, how can you specify the id to be bigint?
@p.matsinopoulos Just replace SERIAL -> BIGSERIAL
|
80 
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.

Improve this answer

2 Comments

This should be marked as answer, and the answer should belong to @resnyanskiy
I had to first drop the pkey and then run this. ALTER TABLE <table> DROP CONSTRAINT <pkey_name>;
23

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

Improve this answer

2 Comments

The problem with this solution is that if the table already contains rows then you get an error: SQL Error [23502]: ERROR: column "id" contains null values
@isapir: There was a bug in early versions (pg 10 and 10.1) producing this error. It was fixed with pg 10.2. Details here: dba.stackexchange.com/q/200143/3684
8

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;
Improve this answer

Comments

3

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

Improve this answer

Comments

3

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
Improve this answer

1 Comment

I'm getting ERROR: column "id" of relation "post" must be declared NOT NULL before identity can be added. How do I update the existing values?
1

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

Improve this answer

2 Comments

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.
This does not provide an answer to the question. Once you have sufficient reputation you will be able to comment on any post; instead, provide answers that don't require clarification from the asker. - From Review

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.