Re: Converting varchar() to text

Robert Treat <xzilla(at)users(dot)sourceforge(dot)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