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

8 Answered Questions

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

3 Answered Questions

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

  • 2012-02-28 21:28:35
  • nicolaskruchten
  • 97757 View
  • 78 Score
  • 3 Answer
  • Tags:   postgresql

1 Answered Questions

[SOLVED] Postgres No Primary Key Drawback

  • 2012-10-01 19:42:53
  • AlexGad
  • 16288 View
  • 37 Score
  • 1 Answer
  • Tags:   postgresql

1 Answered Questions

[SOLVED] Safely rename tables using serial primary key columns

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
  • 431105 View
  • 251 Score
  • 7 Answer
  • Tags:   sql postgresql

1 Answered Questions

[SOLVED] How to assign serial as a primary key for the existing data?

  • 2017-03-24 14:00:39
  • aquib iqbal
  • 39 View
  • -1 Score
  • 1 Answer
  • Tags:   postgresql

1 Answered Questions

[SOLVED] Sequence incremented randomly postgres

1 Answered Questions

[SOLVED] Is a 'serial' column automatically a primary key in PostgreSQL?

1 Answered Questions

[SOLVED] How to convert integer to serial

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

Sponsored Content