Hi all,

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
sequence could change it's type so that a new implicit sequence will be
created, set with its current value set to the highest value 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.

So if you had:

CREATE TABLE stuff (id int, content text);

INSERT INTO stuff (id, content) values (1,'alpha'),(2,'beta'),(5,'gamma');

You could just issue:

ALTER TABLE stuff ALTER COLUMN id TYPE serial;

And continue as so:

INSERT INTO stuff (content) values ('delta');

SELECT id from stuff;

 id
----
  1
  2
  5
  6
(4 rows)

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.

Abhorrent idea, or acceptable?

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

Responses

• Re: Alter column to type serial at 2010-11-04 14:04:59 from Tom Lane
• Re: Alter column to type serial at 2010-11-04 16:13:04 from Alvaro Herrera

pgsql-hackers by date

Next:	From: Kenneth Marshall	Date: 2010-11-04 12:35:39
	Subject: Re: Hash support for arrays
Previous:	From: Peter Eisentraut	Date: 2010-11-04 11:20:39
	Subject: Re: why does plperl cache functions using just a bool for is_trigger