Thread: 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
From
bradsnobar@netscape.net (Bradley D. Snobar)
Date:
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