varchar to text

[Stefan Holzheu]

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

Regards

Stefan
--
-----------------------------
Dr. Stefan Holzheu
Tel.: [phone]
Fax.: [phone]
BITOeK Wiss. Sekretariat
Universitaet Bayreuth
D-95440 Bayreuth
-----------------------------

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[scott.marlowe]

Post by Stefan Holzheu
UPDATE pg_attribute SET atttypid = 25, atttypmod=-1 where attrelid
=(select oid from pg_class where relname='table_name') and atttypid=1043;
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...


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings