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

Bradley D. Snobar wrote:
> I'm confused, this looks fairly unrelated to the original message that I had sent?

Unrelated?  You mean the original column was bigint, and not a serial.
Oh, sorry, I read it wrong.

---------------------------------------------------------------------------


>
> Bruce Momjian <pgman@candle.pha.pa.us> wrote:
>
> >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
> >
>
> __________________________________________________________________
> Switch to Netscape Internet Service.
> As low as $9.95 a month -- Sign up today at http://isp.netscape.com/register
>
> Netscape. Just the Net You Need.
>
> New! Netscape Toolbar for Internet Explorer
> Search from anywhere on the Web and block those annoying pop-ups.
> Download now at http://channels.netscape.com/ns/search/install.jsp
>

--
  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