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

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: "Bradley D(dot) Snobar" <bradsnobar(at)netscape(dot)net>
Cc: Alvaro Herrera <alvherre(at)dcc(dot)uchile(dot)cl>, pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #1434: ERROR: type "bigserial" does not exist
Date: 2005-01-30 14:19:03
Message-ID: 200501301419.j0UEJ3b02055@candle.pha.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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(at)candle(dot)pha(dot)pa(dot)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(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
> >
>
> __________________________________________________________________
> 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(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

In response to

Browse pgsql-bugs by date

  From Date Subject
Next Message Kim Hansen 2005-01-31 01:14:45 BUG #1451: FATAL: could not reattach to shared memory
Previous Message Heiko Nocon 2005-01-30 11:20:10 Re: BUG #1443: Can't start service of Postgres