I have a PostgreSQL table with existing data.
How do I add an auto-incrementing primary key without deleting and re-creating the table?
Add a comment
|
7 Answers
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');
18 Comments
msbyuva
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
Synesso
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.
leonbloy
@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.
p.matsinopoulos
When using the
ALTER TABLE <table> ADD COLUMN id SERIAL PRIMARY KEY, how can you specify the id to be bigint?
Israel Lins Albuquerque
@p.matsinopoulos Just replace SERIAL -> BIGSERIAL
|
ALTER TABLE test1 ADD COLUMN id SERIAL PRIMARY KEY;
This is all you need to:
- Add the
idcolumn - Populate it with a sequence from 1 to count(*).
- Set it as primary key / not null.
Credit is given to @resnyanskiy who gave this answer in a comment.
2 Comments
Eric
This should be marked as answer, and the answer should belong to @resnyanskiy
Josh Robertson
I had to first drop the pkey and then run this.
ALTER TABLE <table> DROP CONSTRAINT <pkey_name>;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/.
2 Comments
isapir
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
Erwin Brandstetter
@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
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;
Comments
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
Comments
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:
- Parameter
GENERATEDBY DEFAULTandALWAYS - Sequence in Postgres
1 Comment
luckydonald
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?ALTER TABLE test1 ADD id int8 NOT NULL GENERATED ALWAYS AS IDENTITY;
2 Comments
Community
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.
Smit Gajera
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
