Re: Converting varchar() to text

On Wednesday 15 September 2004 12:29, Steve Atkins wrote:
> Is there a safe way to convert varchar(n) to text, other than create
> a new column, update, delete column, rename?
>
> I have a number of databases that were built with varvhar(n) and which
> should have been done with text. They're in production, and I'd rather
> not take the downtime needed to convert some rather large tables - the
> bulk update hitting every row of the large table makes it effectively
> unvacuumable, and vacuum full requires locks that effectively shut
> down the entire system.
>
> They're the same format on disk, so I'm guessing that some diddling
> with pg_attribute may be possible. Does anyone have any experience
> doing this?
>

I wouldn't say it's impossible to do it, but several people have reported
corruption issues in things like indexes when doing this type of thing in
7.4.x. If you really cant do it the normal way, if you dont have too many
trigger issues, is to create a new table via a select statement with the
proper columns, then drop the old table and rename the new one. Another idea
might be to just add the new column and then use a view with some coalesce
magic to combine the two columns into one. HTH

--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL