BUG #1434: 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

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

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