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

From: bradsnobar(at)netscape(dot)net (Bradley D(dot) Snobar)
To: pgman(at)candle(dot)pha(dot)pa(dot)us (Bruce Momjian), alvherre(at)dcc(dot)uchile(dot)cl (Alvaro Herrera)
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #1434: ERROR: type "bigserial" does not exist
Date: 2005-01-30 07:22:44
Message-ID: 4208E368.5BBFAD32.0EA262E6@netscape.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

I'm confused, this looks fairly unrelated to the original message that I had sent?

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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Heiko Nocon 2005-01-30 11:20:10 Re: BUG #1443: Can't start service of Postgres
Previous Message Tom Lane 2005-01-30 06:46:19 Re: [BUGS] Bug in create operator and/or initdb