10

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?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Damir
  • 54,277
  • 94
  • 246
  • 365
  • 1
    "but it doesn'twork" isn't a proper issue explanation. – zerkms Dec 05 '14 at 02:44
  • @zerkms Sorry, I convertedto bigint but I want to be serial, if there is a difference – Damir Dec 05 '14 at 02:53
  • If you've been able to convert your primary key into `bigint` then add a default value of a sequence that the first value would be the greatest value + 1. – Luc M Dec 05 '14 at 03:01
  • `serial` is 4 bytes and has the same limits as `integer` Reference: http://www.postgresql.org/docs/9.3/static/datatype-numeric.html – zerkms Dec 05 '14 at 03:17
  • The statement you're using already converted it to a bigint. The only thing missing is adding a sequence as the default value, in the event it's not already there. – Denis de Bernardy Dec 05 '14 at 14:18

1 Answers1

21

serial is a pseudo data type, not an actual data type. It's an integer underneath with some additional DDL commands executed automatically:

  1. Create a SEQUENCE (with matching name by default).
  2. Set the column NOT NULL and the default to draw from that sequence.
  3. Make the column "own" the sequence.

Details:

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 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:

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228