Re: BUG #1434: ERROR: type "bigserial" does not exist

Alvaro Herrera wrote:
> On Sat, Jan 22, 2005 at 10:28:16PM +0000, Brad Snobar wrote:
>
> > The column was a primary key bigint.
> >
> > ALTER TABLE "public"."CategoryBuildingRankSchemas"
> > ALTER COLUMN "IDCategoryBuildingRankSchema" TYPE BIGSERIAL;
> >
> > ERROR: type "bigserial" does not exist
>
> Bigserial is not a type. Rather, it's a type "with strings
> attached". You can achieve the same effect by using
>
> alter table foo alter column a type bigint,
> alter column a set default nextval('seq');
>
> Sadly, you have to create the sequence by hand, and it won't be dropped
> when the table is dropped.

I tried just altering the column from 'integer' to 'bigint' and it
seemed to work:

test=> create table test (x serial);
NOTICE: CREATE TABLE will create implicit sequence "test_x_seq" for serial column "test.x"
CREATE TABLE
test=> \d test
Table "public.test"
Column | Type | Modifiers
--------+---------+-----------------------------------------------------
x | integer | not null default nextval('public.test_x_seq'::text)

test=> alter table test alter column x type bigint;
ALTER TABLE
test=> \d test
Table "public.test"
Column | Type | Modifiers
--------+--------+-----------------------------------------------------
x | bigint | not null default nextval('public.test_x_seq'::text)

All sequences are bigint so there is nothing to change there.

So, I think the trick is to change the underlying column type but not
change the default which is tied to the sequence.

This certainly is an interesting usage report.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073