PostgreSQL column type conversion from bigint to bigserial

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

ALTERing a column from BIGINTEGER to BIGSERIAL in order to make it auto-increment won't work. BIGSERIAL is not a true type, it is a trick that automates PK and SEQUENCE creation.

Instead you can create a sequence yourself, then assign it as the default for a column:

CREATE SEQUENCE "YOURSCHEMA"."SEQNAME";ALTER TABLE "YOURSCHEMA"."TABLENAME"   ALTER COLUMN "COLUMNNAME" SET DEFAULT nextval('"YOURSCHEMA"."SEQNAME"'::regclass);ALTER TABLE "YOURSCHEMA"."TABLENAME" ADD CONSTRAINT pk PRIMARY KEY ("COLUMNNAME");

This is a simple workaround:

ALTER TABLE table_name drop column column_name, add column column_name bigserial;