From: | Ian Barwick <barwick(at)gmx(dot)net> |
---|---|
To: | Andre Schubert <andre(at)km3(dot)de>, pgsql-admin(at)postgresql(dot)org |
Subject: | Re: Length of Varchar |
Date: | 2003-01-24 10:33:33 |
Message-ID: | 200301241133.33319.barwick@gmx.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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(at)gmx(dot)net
From | Date | Subject | |
---|---|---|---|
Next Message | Hosen, John | 2003-01-24 14:00:55 | PostgreSQL 7.2.3 and RedHat Advanced Server |
Previous Message | Ian Barwick | 2003-01-24 08:26:45 | Re: Length of Varchar |