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@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073