On Fri, 2003-01-24 at 05:33, Ian Barwick wrote:
> 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" ;-)
>
There is a "gamblers lunch", for those willing to risk total system
failure and the lives of there first born child. You can issue the
following query to find out the attribute information for the field you
want to modify
select a.* from pg_attribute a, pg_class c where c.relname='tablename'
and a.attnum > 0 and a.attrelid = c.oid;
if you then do:
update pg_attribute set atttypmod=N where attrelid=(select oid from
pg_class where relname='tablename') and attname='fieldname';
where N = length of desired field + 4.
you can then run the first query to verify the changes.
Robert Treat
