Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-bugs by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group