PostgreSQL column type conversion from bigint to bigserial

Question

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 1

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