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?
Add a comment
|
4 Answers
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
2 Comments
Alkanshel
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?
Nick Barnes
@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 instead.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");
1 Comment
Craig Ringer
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, LOCKing the table first to prevent concurrent INSERTs.This is a simple workaround:
ALTER TABLE table_name drop column column_name, add column column_name bigserial;
2 Comments
Alexander
as you say, this is a hack. He would lose all data that is stored in stat column. Bad solution!
Elias Kabir Noyon
@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.
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.
1 Comment
search-learn
this is not an answer - this is an opinion