On Sat, Jan 20, 2007 at 11:19:50AM -0600, Kelly Burkhart wrote: > On 1/20/07, Shoaib Mir <shoaibmir@xxxxxxxxx> 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@xxxxxxxxx EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
