Skip site navigation (1) Skip section navigation (2)

Re: varchar to text

From: "scott(dot)marlowe" <scott(dot)marlowe(at)ihs(dot)com>
To: Stefan Holzheu <stefan(dot)holzheu(at)bitoek(dot)uni-bayreuth(dot)de>
Cc: ADMIN <pgsql-admin(at)postgresql(dot)org>
Subject: Re: varchar to text
Date: 2004-04-13 15:33:51
Message-ID: Pine.LNX.4.33.0404130928370.28757-100000@css120.ihs.com (view raw or flat)
Thread:
Lists: pgsql-admin
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...  


In response to

pgsql-admin by date

Next:From: Greg SpiegelbergDate: 2004-04-13 16:33:05
Subject: Re: Postgres Admin - Export Database
Previous:From: Nichlas LöfdahlDate: 2004-04-13 15:26:01
Subject: log_min_cost_statement

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group