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
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
