By Damir


2014-12-05 02:34:20 8 Comments

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?

1 comments

@Erwin Brandstetter 2014-12-05 05:22:50

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

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.

Related Questions

Sponsored Content

9 Answered Questions

[SOLVED] How to exit from PostgreSQL command line utility: psql

9 Answered Questions

[SOLVED] Sqlite primary key on multiple columns

7 Answered Questions

[SOLVED] How to set auto increment primary key in PostgreSQL?

  • 2011-10-10 20:56:40
  • mkn
  • 446150 View
  • 259 Score
  • 7 Answer
  • Tags:   sql postgresql

3 Answered Questions

[SOLVED] Adding 'serial' to existing column in Postgres

  • 2012-02-28 21:28:35
  • nicolaskruchten
  • 111743 View
  • 90 Score
  • 3 Answer
  • Tags:   postgresql

1 Answered Questions

[SOLVED] Safely rename tables using serial primary key columns

1 Answered Questions

[SOLVED] Sequence incremented randomly postgres

2 Answered Questions

[SOLVED] Changing primary key int type to serial

1 Answered Questions

[SOLVED] How to convert integer to serial

  • 2012-09-25 05:25:00
  • Sreesha
  • 11971 View
  • 19 Score
  • 1 Answer
  • Tags:   postgresql

Sponsored Content