Re: ALTER TABLE & COLUMN

Neil Conway writes:

> As for changing the type of a column, how would this be anything more
> than syntactic sugar over renaming the existing column to a temp name,
> adding a new column with the appropriate type & name, moving the data
> from the old column to the new one, and dropping the old one?

I agree with you that it is "just" syntactic sugar, but I like sweets ;-). For
example, from 7.2 to 7.3 we got DROP COLUMN which I think is really nice. In
the 7.2 documentation there is this example, how to "drop" a column:

CREATE TABLE temp AS SELECT did, city FROM distributors;
DROP TABLE distributors;
CREATE TABLE distributors (
did DECIMAL(3) DEFAULT 1,
name VARCHAR(40) NOT NULL
);
INSERT INTO distributors SELECT * FROM temp;
DROP TABLE temp;

But users like me (and I guess even some admins) think "uuhhh, how do I do
this with a table with 150 columns, what happens to the triggers, I can make
so many errors and screw things up...". Now we have the syntactic glue and I
can tell the db to drop one out of the 150 columns and the chance that I mess
up the database are much smaller than before.

> Since a lot of data type changes are not obvious (how do you convert the
> data from one type to another, in all cases?), I don't see a real
> problem leaving this in the hands of the admin. The one exception might
> be changing the length limit on a varchar(n) column, but that's such a
> small case I'm not sure it's worth the bother (and can be done by
> hacking the system catalogs anyway).

What about this:

If the conversion can be done with a normal built in Postgresql cast, let's
use that one, e.g. from text to integer use the "normal" cast. If the user
wants some strange stuff, he has to define a conversion function. If we have
something non-obvious like circle -> integer, we need a user supplied
function. The syntax could be something like:

ALTER TABLE sometable
ALTER COLUMN somecolumn SET TYPE TO integer USE FUNCTION circle2int

(maybe without the TO and USING instead of USE)

Then each element of the new column would be set to new = circle2int(old).

Just an idea...

Cheers

Tilo