Re: Length of Varchar

On Friday 24 January 2003 07:37, Andre Schubert wrote:

> i have a little question on changing the length of a varchar field.
> Is there another way than dump and reload if i want to change the length
> of a varchar field ?

in 7.3:

BEGIN;
ALTER TABLE foo RENAME your_field TO your_field_old;
ALTER TABLE foo ADD COLUMN your_field VARCHAR([new length]);
UPDATE foo SET your_field=your_field_old;
ALTER TABLE foo DROP COLUMN your_field_old;
COMMIT;

In 7.3, if the new column is shorter you may need to truncate the values
being inserted.

In versions < 7.3 you will not be able to drop the old column; there you
may want to recreate the table, there's a techdoc article here:
http://techdocs.postgresql.org/techdocs/updatingcolumns.php

> I have search the idocs and found some docs about the system-table
> pg_attribute, where the length of a varchar-field is stored in atttypmod.
> Is it possible to change the value of atttypmod and is it safe to change
> this value?

Possible but probably not safe. No doubt someone will be along shortly
with a more accurate opinion ;-).

Ian Barwick
barwick(at)gmx(dot)net