Re: Converting varchar() to text

On Fri, Sep 17, 2004 at 10:56:36AM -0400, Tom Lane wrote:
> 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.

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