CODEWITHSUNDEEP
sqlpostgresql
Nathaniel Ford
Nathaniel Ford

Reputation: 21220

Convert BIGSERIAL to BIGINT

I have a table defined across two scripts as follows (I'm eliding unneeded details):

CREATE TABLE a (id BIGSERIAL NOT NULL PRIMARY KEY);
ALTER TABLE IMAGE ADD COLUMN b_id BIGSERIAL;

This results in a table with a column b_id that is a BIGSERIAL, and is a foreign key to table b from a. This is incorrect, however, and I need to alter the table (safely) to drop the SERIAL part and leave it as a BIGINT. (Essentially, that column should not insert an autoincremented number if there is an insert that doesn't specify the value.)

The documentation indicates that a SERIAL just adds a sequence to the underlying datatype, and there are plenty of results for how to do this manually if you want to move from an integer-based type to a serial-based type, but not the reverse. The documentation says:

You can drop the sequence without dropping the column, but this will force removal of the column default expression.

But the documentation to DROP SEQUENCE is remarkably light in terms of what name to use and what the effects of this are.

Upvotes: 3

Views: 7352

Answers (1)

Daniel Lyons
Daniel Lyons

Reputation: 22803

BIGSERIAL is already BIGINT, it just automatically adds the sequence. You can see it if you do \d a. You will see something like this:

                         Table "public.a"
 Column |  Type  |                   Modifiers
--------+--------+------------------------------------------------
 id     | bigint | not null default nextval('a_id_seq'::regclass)
Indexes:
    "a_pkey" PRIMARY KEY, btree (id)

So right there you see the sequence name: a_id_seq. So drop that.

=# DROP SEQUENCE a_id_seq CASCADE;
NOTICE:  drop cascades to default for table a column id
DROP SEQUENCE

=# \d a
      Table "public.a"
 Column |  Type  | Modifiers
--------+--------+-----------
 id     | bigint | not null
Indexes:
    "a_pkey" PRIMARY KEY, btree (id)

Upvotes: 7

Related Questions