34

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?

Nick Barnes
  • 19,816
  • 3
  • 51
  • 63
k s l kumar
  • 351
  • 1
  • 3
  • 3

4 Answers4

41

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
Nick Barnes
  • 19,816
  • 3
  • 51
  • 63
  • 1
    I don't feel like this really answered the question as to how to get bigserial on an existing column. Are you saying that setting a serial on bigint will result in it being bigserial? – Alkanshel Jul 06 '18 at 20:22
  • 3
    @Amalgovinus: Basically, yes. Postgres doesn't actually keep track of whether a column was created as a `SERIAL` type. If you look at the `pg_dump` output for a table with a `BIGSERIAL`, you'll just see a `BIGINT` and a sequence (and creating the `BIGINT` and the sequence yourself is exactly the same as using the `BIGSERIAL` keyword). Note that, as of Postgres 10, you have the option of turning it into an [identity column](https://blog.2ndquadrant.com/postgresql-10-identity-columns/) instead. – Nick Barnes Jul 07 '18 at 00:03
11

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");
Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
Vao Tsun
  • 47,234
  • 13
  • 100
  • 132
  • 3
    Note that this will *not* populate the column with existing values. So the addition of the `PRIMARY KEY` constraint will fail if there are existing rows that are `NULL` or non-unique. (You're assuming the user doesn't already have a primary key though. `SERIAL` isn't just for a PK, and `SERIAL` does not imply `SERIAL PRIMARY KEY`). Additionally, if the table has existing rows, you will want to `setval` the sequence to ensure it starts at the first free identifier, `LOCK`ing the table first to prevent concurrent `INSERT`s. – Craig Ringer Aug 12 '15 at 13:50
0

This is a simple workaround:

ALTER TABLE table_name drop column column_name, add column column_name bigserial;
Rishav
  • 3,818
  • 1
  • 31
  • 49
Elias Kabir Noyon
  • 59
  • 3
  • 11
    as you say, this is a *hack*. He would *lose* all data that is stored in stat column. Bad solution! – Alexander May 08 '18 at 19:12
  • 2
    @Alexander, You didn't quite get it right. Whatever the old values are, when someone decides the column type to be *serial, he must create and use sequence. If that doesn't serve right, hacks are fathomless though. One can add an extra column, copy old values, appy my hack, update values with old values, and finally ALTER SEQUENCE to set the appropriate nextval(). Hope this helps. – Elias Kabir Noyon May 08 '18 at 19:53
-4

Sounds like alot of professionals out there on this subject... if the original table did indeed have data then the real answer to this dilemma is to have designed the db correctly in the first place. However, that being the case, to change the column rule (type) would require integrity verification of that column for the new paradigm. And, don't forget, anywhere where that column is manipulated (added/updated) then that would need to be looked into.

If it's a new table then okay, simples: delete column and re-add new column (takes care of the sequence for you). Again, design, design, design.

I think we've all fouled on this.

John Smith
  • 1