Re: full featured alter table?

Hi,

Reynard Hilman wrote:
> 
>> OK, so how do we handle things like converting a varchar to a 
>> timestamp with time zone?  What if one of the rows has invalid date 
>> syntax?  Do we convert the column anyway, or throw the whole change 
>> out with an error?
>>
> being a developer instead of DBA makes me think little about the danger 
> of losing data when you change column type. But, I think you're right, 
> very BAD things could happen with your data if the database silently 
> convert / truncate your data when you change the column type.
> As far as I remember when working with Oracle, it allows you to change 
> the data as long as you don't lose or corrupt your data. So, for 
> example, changing varchar(20) to varchar(40) should be ok, but the 
> reverse might not be ok. It'd be nice if it allows you to change the 
> type from varchar(40) to varchar(20) if you don't have any data that is 
> larger than varchar(20). I don't know how much complexity that will add, 
> though. well that's just some idea from me.

This of course would be convenient, but otoh it would add a great value
of complexity to the backend for a rarely used feature. At least it
should be rarely used :)
For the rare cases where someone has to change column type, maybe its
more easy if (s)he does this by hand - thus not complaining to the
overloaded core-developers about data loss or unexpected results.
The only thing which is a bit complicated is the disabling/enabling
trigger sequence which can be borrowed from pg_dump output.
The remaining part is possible to put all in one transaction;
or at least the delete rows/copy back part:
create table ... as select .... from original ...;
delete from original;
alter table original create column (with new type)
alter table remove old column
insert into original select * from temptable

Regards
Tino