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: (view raw, whole thread or download thread mbox)
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"
	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;
	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                        |
  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-2017 The PostgreSQL Global Development Group