Обсуждение: Alter column to type serial

Hi all,<br /><br />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)?  In effect this would mean that users who forgot to set up a
sequencecould change it's type so that a new implicit sequence will be created, set with its current value set to the
highestvalue of whatever column it was bound to.  This thought was triggered by a user on IRC wishing to migrate from
MySQL,but had tables with some sort of ID column without any associated sequence.<br /><br />So if you had:<br /><br
/>CREATETABLE stuff (id int, content text);<br /><br />INSERT INTO stuff (id, content) values
(1,'alpha'),(2,'beta'),(5,'gamma');<br/><br />You could just issue:<br /><br />ALTER TABLE stuff ALTER COLUMN id TYPE
serial;<br/><br />And continue as so:<br /><br />INSERT INTO stuff (content) values ('delta');<br /><br />SELECT id
fromstuff;<br /><br /> id<br />----<br />  1<br />  2<br />  5<br />  6<br />(4 rows)<br /><br />This would be instead
ofhaving to do:<br /><br />CREATE SEQUENCE id_stuff_seq;<br /><br />SELECT setval('id_stuff_seq', (SELECT max(id) FROM
stuff))<br/><br />ALTER TABLE stuff ALTER COLUMN id SET DEFAULT nextval('id_stuff_seq'::regclass);<br /><br />Which
wouldalso mean the sequence would not get dropped with the table.<br /><br />Abhorrent idea, or acceptable?<br
clear="all"/><br />-- <br />Thom Brown<br />Twitter: @darkixion<br />IRC (freenode): dark_ixion<br />Registered Linux
user:#516935<br /> 

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

<div class="gmail_quote">On 4 November 2010 14:04, Tom Lane <span dir="ltr"><<a
href="mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>></span>wrote:<br /><blockquote class="gmail_quote"
style="margin:0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;"><div class="im">Thom
Brown<<a href="mailto:thom@linux.com">thom@linux.com</a>> writes:<br /> > Would it be possible (or reasonable)
toadd support for changing the type of<br /> > a column to serial or bigserial (yes, yes, I know they're not
actual<br/> > types)?<br /><br /></div>We've looked at that in the past and decided there were enough corner<br />
casesthat it wasn't clearly a good idea.  In particular, what do you do<br /> with the existing data in the column?
 Whatdo you do if there's already<br /> a DEFAULT expression for the column, throw it away?  In particular, what<br />
ofthe special case that the column is in fact already a serial, so the<br /> default is pointing at an existing
sequence?<br/><br /> It is possible to accomplish everything that such a command would do<br /> manually, so the
argumentfor having it boils down to wanting it to<br /> be a bit easier.  But unless the command can always do the
rightthing<br /> automatically, I'm not sure "easy" is a good argument.<br /><br /> There's also the objection that
suchan operation would actually have<br /> very little to do with ALTER COLUMN TYPE --- most of the things it would<br
/>do are not that.  The fact that serial was bolted on as a fake type is a<br /> wart that maybe we shouldn't extend in
thisparticular fashion.<br /><br /></blockquote></div><br />I suspected this may have been discussed previously, I just
failedto find it.  And yes, it's purely for simplification, and to auto-clean sequences when tables are dropped.  I
didn'tthink it would be straightforward, but clearly there are show-stoppers abound.<br /><br />Thanks for the reply
though.:)<br clear="all" /><br />-- <br />Thom Brown<br />Twitter: @darkixion<br />IRC (freenode): dark_ixion<br
/>RegisteredLinux user: #516935<br /> 

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