Re: Alter definition of a column

On Sat, Jan 20, 2007 at 11:19:50AM -0600, Kelly Burkhart wrote:
> On 1/20/07, Shoaib Mir <shoaibmir(at)gmail(dot)com> wrote:
> >Should help --> ALTER TABLE tablename ALTER columname TYPE text;
>
> I was looking for a way to alter a column from varchar(n) to text
> without using the alter command and consequently touching every single
> row. Below is sql which seems to work, but is it advisable to do such
> shenanigans? (are varchar and text the same thing)?

text == varchar, which is varchar(x) without a limit. But the storage in
the table is the same in all 3 cases (in fact, the storage for char is
also the same).

> kelly=# create table foo( c1 varchar(4) not null, c2 text not null );
> CREATE TABLE
> kelly=#
> kelly=# update pg_attribute set atttypid=25, atttypmod=-1
> kelly-# where attname = 'c1' and attrelid =
> kelly-# (select oid from pg_class where relname = 'foo');
> UPDATE 1
> kelly=#
> kelly=# \d foo
> Table "public.foo"
> Column | Type | Modifiers
> --------+------+-----------
> c1 | text | not null
> c2 | text | not null

I know there's some considerations when altering system tables like
this; the archives probably have more details. You might be a bit safer
doing that in a database with no other connections. But in the case of
increasing a size limit (or removing one), ALTER shouldn't have to
re-read the entire table. AFAIK the only reason it does so right now is
it doesn't have the brains to know what cases it doesn't need to do this
on.

Also, you could replace that pg_class sub-select with "'foo'::regclass".
--
Jim Nasby jim(at)nasby(dot)net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)