Alter column to type serial

Thom Brown <thom@linux.com> writes:

Would it be possible (or reasonable) to add support for changing the type of
a column to serial or bigserial (yes, yes, I know they're not actual
types)?

We've looked at that in the past and decided there were enough corner
cases that it wasn't clearly a good idea. In particular, what do you do
with the existing data in the column? What do you do if there's already
a DEFAULT expression for the column, throw it away? In particular, what
of the special case that the column is in fact already a serial, so the
default is pointing at an existing sequence?

It is possible to accomplish everything that such a command would do
manually, so the argument for having it boils down to wanting it to
be a bit easier. But unless the command can always do the right thing
automatically, I'm not sure "easy" is a good argument.

There's also the objection that such an operation would actually have
very little to do with ALTER COLUMN TYPE --- most of the things it would
do are not that. The fact that serial was bolted on as a fake type is a
wart that maybe we shouldn't extend in this particular fashion.

regards, tom lane

On 4 November 2010 14:04, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Thom Brown <thom@linux.com> writes:

Would it be possible (or reasonable) to add support for changing the type

of

a column to serial or bigserial (yes, yes, I know they're not actual
types)?

We've looked at that in the past and decided there were enough corner
cases that it wasn't clearly a good idea. In particular, what do you do
with the existing data in the column? What do you do if there's already
a DEFAULT expression for the column, throw it away? In particular, what
of the special case that the column is in fact already a serial, so the
default is pointing at an existing sequence?

It is possible to accomplish everything that such a command would do
manually, so the argument for having it boils down to wanting it to
be a bit easier. But unless the command can always do the right thing
automatically, I'm not sure "easy" is a good argument.

There's also the objection that such an operation would actually have
very little to do with ALTER COLUMN TYPE --- most of the things it would
do are not that. The fact that serial was bolted on as a fake type is a
wart that maybe we shouldn't extend in this particular fashion.

I suspected this may have been discussed previously, I just failed to find
it. And yes, it's purely for simplification, and to auto-clean sequences
when tables are dropped. I didn't think it would be straightforward, but
clearly there are show-stoppers abound.

Thanks for the reply though. :)

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

Thom Brown <thom@linux.com> writes:

I suspected this may have been discussed previously, I just failed to find
it. And yes, it's purely for simplification, and to auto-clean sequences
when tables are dropped. I didn't think it would be straightforward, but
clearly there are show-stoppers abound.

The latest thread I can find on the matter is
http://archives.postgresql.org/pgsql-general/2008-11/msg00825.php

although I seem to remember others.

regards, tom lane

Excerpts from Thom Brown's message of jue nov 04 09:05:01 -0300 2010:

This would be instead of having to do:

CREATE SEQUENCE id_stuff_seq;

SELECT setval('id_stuff_seq', (SELECT max(id) FROM stuff))

ALTER TABLE stuff ALTER COLUMN id SET DEFAULT
nextval('id_stuff_seq'::regclass);

Which would also mean the sequence would not get dropped with the table.

You can fix that with an ALTER SEQUENCE OWNED BY.

Abhorrent idea, or acceptable?

I think the problem is in locking the table against futher insertions
while you do the setval.

--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support