How to convert primary key from integer to serial?
In a Postgres 9.3 table I have an integer
as primary key with automatic sequence to increment, but I have reached the maximum for integer
. How to convert it from integer
to serial
?
I tried:
ALTER TABLE my_table ALTER COLUMN id SET DATA TYPE bigint;
But the same does not work with the data type serial
instead of bigint
. Seems like I cannot convert to serial
?
serial
is a pseudo data type, not an actual data type. It's an integer
underneath with some additional DDL commands executed automatically:
- Create a sequence (with matching name by default).
- Set the column
NOT NULL
and the default to draw from that sequence. - Make the column "own" the sequence.
Details:
- Safely and cleanly rename tables that use serial primary key columns in Postgres?
A bigserial
is the same, built around a bigint
column. You want bigint
, but you already achieved that. To transform an existing serial
column into a bigserial
(or smallserial
), all you need to do is to ALTER
the data type of the column. Sequences are generally based on bigint
, so the same sequence can be used for any integer
type.
To "change" a bigint
into a bigserial
or an integer
into a serial
, you just have to do the rest by hand:
- Creating a PostgreSQL sequence to a field (which is not the ID of the record)
The actual data type is still integer
/ bigint
. Some clients like pgAdmin will display the data type serial
in the reverse engineered CREATE TABLE
script, if all criteria for a serial
are met.