Обсуждение: BUG #1434: ERROR: type "bigserial" does not exist
The following bug has been logged online: Bug reference: 1434 Logged by: Brad Snobar Email address: bradsnobar@netscape.net PostgreSQL version: 8.0 Operating system: Linux Description: ERROR: type "bigserial" does not exist Details: The column was a primary key bigint. ALTER TABLE "public"."CategoryBuildingRankSchemas" ALTER COLUMN "IDCategoryBuildingRankSchema" TYPE BIGSERIAL; ERROR: type "bigserial" does not exist
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.
--
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
"Right now the sectors on the hard disk run clockwise, but I heard a rumor that
you can squeeze 0.2% more throughput by running them counterclockwise.
It's worth the effort. Recommended." (Gerry Pourwelle)
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
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
Re: BUG #1434: ERROR: type "bigserial" does not exist
От
bradsnobar@netscape.net (Bradley D. Snobar)
Дата:
I'm confused, this looks fairly unrelated to the original message that I had sent?
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