On Friday 24 January 2003 09:26, Ian Barwick wrote:
> 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
Addendum:
Someone has written me a private email pointing out that if you do
recreate a table like this, dependent views etc. will no longer work,
and asks if there is any way around this apart from upgrading
to 7.3. I think the short answer is "no". (As in there's "no such thing
as a free lunch" ;-)
Ian Barwick
barwick@gmx.net