Re: changing the size of a column without dump/restore

On Mon, Nov 25, 2002 at 01:57:28PM -0500, Michael Richards wrote:
> I've got a huge database table and I need to increase the size of a 
> varchar from like 100 to 200 characters. As I recall the size is just 
> a restriction and doesn't actually affect the format of the table 
> file. 
> 
> Rather than dumping/restoring a 5Gb table with 20,000,000 rows which 
> will take all day and night, is there anything I can twiddle in the 
> system tables to change this size? I'd of course be backing up the 
> data just in case!

PG doesn't have an 'alter table' to increase the column size of a varchar. 
But you can accomplish it by manipulating the system tables directly. The size
of a varchar is stored in pg_attribute as the actual size + 4.

For example to change a column "foo" in table "bar" to 200:

update pg_attribute   set atttypmod = 204 where attrelid = ( select oid                      from pg_class
      where relname = 'bar' ) 
 
and attname = 'foo';

-Roberto

P.S: I don't know if this has any bad side effects.

-Roberto

-- 
+----|        Roberto Mello   -    http://www.brasileiro.net/  |------+
+       Computer Science Graduate Student, Utah State University      +
+       USU Free Software & GNU/Linux Club - http://fslc.usu.edu/     +
* JHM wonders what Joey did to earn "I'd just like to say, for the record, that Joey rules."-- Seen on #Debian