Thread: Converting varchar() to text

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?

Cheers,
  Steve

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

Robert Treat <xzilla@users.sourceforge.net> writes:
> 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 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.

My recollection is that the things that break worst are views that
reference the changed column; you'll need to drop and recreate those,
with possibly cascading effects to other views.

Indexes and foreign keys involving the changed column should also be
dropped and remade, but that's at least fairly localized.

If you have functions that take or return the table rowtype, you might
have some issues there too.

If you want to try it, I'd suggest making a schema dump of your DB
(pg_dump -s) and trying the process on that in a scratch database.

The actual magic is along the lines of

    update pg_attribute set atttypid = 'text'::regtype, atttypmod = -1
    where attrelid = 'mytable'::regclass and attname = 'mycol';

            regards, tom lane

On Fri, Sep 17, 2004 at 10:56:36AM -0400, Tom Lane wrote:
> Robert Treat <xzilla@users.sourceforge.net> writes:
> > 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 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.
>
> My recollection is that the things that break worst are views that
> reference the changed column; you'll need to drop and recreate those,
> with possibly cascading effects to other views.
>
> Indexes and foreign keys involving the changed column should also be
> dropped and remade, but that's at least fairly localized.
>
> If you have functions that take or return the table rowtype, you might
> have some issues there too.

Thanks, Tom. That's the sort of gotchas I was looking for.

> If you want to try it, I'd suggest making a schema dump of your DB
> (pg_dump -s) and trying the process on that in a scratch database.
>
> The actual magic is along the lines of
>
>     update pg_attribute set atttypid = 'text'::regtype, atttypmod = -1
>     where attrelid = 'mytable'::regclass and attname = 'mycol';

I have the luxury of development and staging servers, so I'll give
this a try.

Cheers,
  Steve