Re: Converting varchar() to text - Mailing list pgsql-general

From Steve Atkins
Subject Re: Converting varchar() to text
Date
Msg-id 20040917161125.GA26021@gp.word-to-the-wise.com
Whole thread Raw
In response to Re: Converting varchar() to text  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-general
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

pgsql-general by date:

Previous
From: Lars Kellogg-Stedman
Date:
Subject: Re: Default value if query returns 0 rows?
Next
From: Tom Lane
Date:
Subject: Re: Default value if query returns 0 rows?