pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedvarchar to text
2+ messages / 2 participants
[nested] [flat]
* varchar to text
@ 2004-04-13 09:49 Stefan Holzheu <stefan.holzheu@bitoek.uni-bayreuth.de>
2004-04-13 15:33 ` Re: varchar to text scott.marlowe <scott.marlowe@ihs.com>
0 siblings, 1 reply; 2+ messages in thread
From: Stefan Holzheu @ 2004-04-13 09:49 UTC (permalink / raw)
To: pgsql-admin
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.: 0921/55-5720
Fax.: 0921/55-5799
BITOeK Wiss. Sekretariat
Universitaet Bayreuth
D-95440 Bayreuth
-----------------------------
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: varchar to text
2004-04-13 09:49 varchar to text Stefan Holzheu <stefan.holzheu@bitoek.uni-bayreuth.de>
@ 2004-04-13 15:33 ` scott.marlowe <scott.marlowe@ihs.com>
0 siblings, 0 replies; 2+ messages in thread
From: scott.marlowe @ 2004-04-13 15:33 UTC (permalink / raw)
To: Stefan Holzheu <stefan.holzheu@bitoek.uni-bayreuth.de>; +Cc: 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...
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2004-04-13 15:33 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-04-13 09:49 varchar to text Stefan Holzheu <stefan.holzheu@bitoek.uni-bayreuth.de>
2004-04-13 15:33 ` scott.marlowe <scott.marlowe@ihs.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox