PostgreSQL column type conversion from bigint to bigserial

sql database postgresql persistence
k s l kumar picture k s l kumar · Aug 12, 2015 · Viewed 23.8k times · Source

When I try to change the data type of a column in a table by alter command...

alter table temp alter column id type bigserial;

I get

ERROR:  type "bigserial" does not exist

How can I change the datatype from bigint to bigserial?

Answer

Nick Barnes picture Nick Barnes · Aug 12, 2015

As explained in the documentation, SERIAL is not a datatype, but a shortcut for a collection of other commands.

So while you can't change it simply by altering the type, you can achieve the same effect by running these other commands yourself:

CREATE SEQUENCE temp_id_seq;
ALTER TABLE temp ALTER COLUMN id SET NOT NULL;
ALTER TABLE temp ALTER COLUMN id SET DEFAULT nextval('temp_id_seq');
ALTER SEQUENCE temp_id_seq OWNED BY temp.id;

Altering the owner will ensure that the sequence is removed if the table/column is dropped. It will also give you the expected behaviour in the pg_get_serial_sequence() function.

Sticking to the tablename_columnname_seq naming convention is necessary to convince some tools like pgAdmin to report this column type as BIGSERIAL. Note that psql and pg_dump will always show the underlying definition, even if the column was initially declared as a SERIAL type.

As of Postgres 10, you also have the option of using an SQL standard identity column, which handles all of this invisibly, and which you can easily add to an existing table:

ALTER TABLE temp ALTER COLUMN id
  ADD GENERATED BY DEFAULT AS IDENTITY

Related questions

How to select the nth row in a SQL database table?

I'm interested in learning some (ideally) database agnostic ways of selecting the nth row from a database table. It would also be interesting to see how this can be achieved using the native functionality of the following databases: SQL Server …

Read More
How to compare dates in datetime fields in Postgresql?

I have been facing a strange scenario when comparing dates in postgresql(version 9.2.4 in windows). I have a column in my table say update_date with type 'timestamp without timezone'. Client can search over this field with only date (i.…

Read More
Best way to check for "empty or null value"

What is best way to check if value is null or empty string in Postgres sql statements? Value can be long expression so it is preferable that it is written only once in check. Currently I'm using: coalesce( trim(stringexpression),…

Read More