ALTER Bigserial error

"Little, Douglas" <DOUGLAS.LITTLE@orbitz.com> writes:

I'm trying to alter in a bigserial on GP/PG 8.1 I'm getting the error

ERROR: type "bigserial" does not exist
SQL state: 42704

I understand that bigserial isn't a 'true' type, that it's a notational convenience.
And that I can alter in the default.

But it is listed in the type table in the doc and I can create new tables with it.
I should be able to use it in an alter.
Is there a way to fix this bug?

It isn't a bug; it could be argued to be a missing feature, but I don't
think it's very high on anyone's priority list to add. Before anything
could happen here there would need to be consensus on how to behave in
all the various corner cases. For instance, should the ALTER override
any pre-existing default expression for the column? If there's not
already a sequence associated with the column, what initial value should
it be created with? Should the ALTER make any attempt to check or
correct the data in the column?

The only case that seems to me to not have some debatable behavior
involved is widening an existing serial column --- and you can do that
now with "ALTER TYPE bigint". Since serial/bigserial are just macros
for column properties that you can set explicitly, it's always possible
to get where you want to go with lower-level operations; and those
operations give you full control over what happens, whereas a
packaged-up "ALTER TYPE bigserial" operation wouldn't.

regards, tom lane

Thanks for the response tom,
I agree it's more of an missing feature.
Regarding the concensus for direction. I'd like to see the product move in the direction of the sql standard.
Overloading types with macro's probably wasn't a good idea, since it leads people like me astray.

Maybe the doc can be strengthened to include your review.
Thanks for the help.

Doug

On Mon, Apr 26, 2010 at 12:59 PM, Little, Douglas
<DOUGLAS.LITTLE@orbitz.com> wrote:

Thanks for the response tom,
I agree it's more of an missing feature.
Regarding the concensus for direction.  I'd like to see the product move in the direction of the sql standard.

Curiously, what does the SQL spec have to say about autoincrement and
defaults and such?

Scott,
Sorry about the late response.

Curiously, what does the SQL spec have to say about autoincrement and
defaults and such?

From http://savage.net.au/SQL/sql-2003-2.bnf.html#identity%20column%20specification

...
Define a column of a base table.

<column definition> ::=
<column name> [ <data type> | <domain name> ] [ <reference scope check> ]
[ <default clause> | <identity column specification> | <generation clause> ]
[ <column constraint definition> ... ] [ <collate clause> ]

<column constraint definition> ::= [ <constraint name definition> ] <column constraint> [ <constraint characteristics> ]

<column constraint> ::=
NOT NULL
| <unique specification>
| <references specification>
| <check constraint definition>

<reference scope check> ::= REFERENCES ARE [ NOT ] CHECKED [ ON DELETE <reference scope check action> ]

<reference scope check action> ::= <referential action>

<identity column specification> ::=
GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY
[ <left paren> <common sequence generator options> <right paren> ]

-----Original Message-----
From: Scott Marlowe [mailto:scott.marlowe@gmail.com]
Sent: Monday, April 26, 2010 3:49 PM
To: Little, Douglas
Cc: Tom Lane; pgsql-general@postgresql.org
Subject: Re: [GENERAL] ALTER Bigserial error

On Mon, Apr 26, 2010 at 12:59 PM, Little, Douglas
<DOUGLAS.LITTLE@orbitz.com> wrote:

Thanks for the response tom,
I agree it's more of an missing feature.
Regarding the concensus for direction. I'd like to see the product move in the direction of the sql standard.

Curiously, what does the SQL spec have to say about autoincrement and
defaults and such?