Re: full featured alter table?

On Thu, 12 Jun 2003, Sven Koehler wrote:

> Hi,
> 
> one of the biggest disease of PostGreSQL is, that i can't change the 
> definition of a column.
> 
> In order to do that, i'd have to drop any keys, drop the column and 
> create a new one with all indexes etc.
> 
> Are there any plans to overcome that problem?
> Even simple changes like varchar(20) to varchar(200) are not allowed.
> 
> I asked this question about 2 years ago, and there were only some guys, 
> that told me that i wouldn't need to change my DB if i'd plan it well.
> 
> So my DB is planned well, but i have to change it every now and than 
> because i must implement the changes that my client demands me to do, 
> and have some extra work that nobody will pay me for, if there's no way 
> to change a column.
> 
> This is the only missing feature, that prevent me to use this DBMS - i'd 
> love to, because it's an ORDBMS and that's what i'd have needed sometimes.

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?

What about converting int8 to int4 where we have integers that are LARGER 
than what an int4 can hold.  Do we just set the int4 to max/min when the 
int8 is out of range?

How about converting varchar(64) to varchar(32).  Do you want it to error 
out if there are any fields over 32 chars, or just chop them off?

MySQL has a "feature" like the one you want.  It's not well thought out, 
and easily causes as much misery and pain as it prevents.  If you convert 
a column to date type it silently converts any field it can't suss out to 
the very wonderful value of 0000-00-00.  Since their DDL is not 
transactable, you've just lost all your dates.  Hope you had a backup 
laying around.

Converting column types is like walking around with a gun pointed at your 
foot.  If the possible error cases aren't well thought out, then bam! the 
gun goes off and your data is so much mush.

Other vendors may not care as much about data integrity as the postgresql 
team, and if you prefer their product feel free to use it.  The Postgresql 
team has a tendency of not incorporating such changes until they can work 
for virtually all cases without error.

Here's how I do it:

# pg_dump -a -t tabletochange >tabledata.sql
# pg_dump -s -t tabletochange >tableschema.sql
vi tableschema.sql (make changes)
psql testdatabase <tableschema.sql
psql testdatabase <tabledata.sql
psql testdatabase

look and see if your data is still there.

Changing column types is playing fast and loose with your data and is not 
recommended.

It's also, as near as I can tell, not in the SQL3 spec.

It may bug you that open source projects say "submit a patch and we'll 
see" but that's way better than "oh yeah, we have that feature.  what?  
Your data got mucked up?  Wow, that's never happened before."