On Tue, 13 Apr 2004, Stefan Holzheu wrote:
> I'd like to alter all columns from type varchar to text. Could I do this by:
>
> UPDATE pg_attribute SET atttypid = 25, atttypmod=-1 where attrelid
> =(select oid from pg_class where relname='table_name') and atttypid=1043;
>
> I just tried on a test database. It worked fine with one exception:
> Views depending on an altered column did not work anymore. After
> recreating the views it was ok.
>
> I know the procedure of "rename column - add column - delete column" but
> it's laborious for a large number of columns. There was also a
> discussion on the list maybe one year ago. Unfortunately I couldn't find
> the thread in the archive.
>
> We are running postgres 7.4.1
The old fashioned way of doing this was to dump the database, change the
appropriate fields in the dump, and reload.
It's certainly faster than the rename add drop column boogie, and cleans
up your data store at the same time. Back in the days of transaction wrap
around and index bloat, it wasn't such a bad thing to do every few months
anyway. :-)
Speaking of which, I just checked, and it appears I've got growing system
index on stats problems in my older 7.2 database, so I'm off dump and
reload it...