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: Alvaro Herrera <alvherre(at)dcc(dot)uchile(dot)cl>
Cc: Brad Snobar <bradsnobar(at)netscape(dot)net>, pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #1434: ERROR: type "bigserial" does not exist
Date: 2005-01-29 16:32:21
Message-ID: 200501291632.j0TGWMa17228@candle.pha.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
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

In response to

pgsql-bugs by date

Next:From: Rolf SponselDate: 2005-01-29 20:06:21
Subject: [Feed-back] Installing PostgreSQL 8.0.0 on SPARC/Solaris. Configure and install issues.
Previous:From: DirkDate: 2005-01-28 20:52:20
Subject: BUG #1450: unknown symbols

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