Skip site navigation (1) Skip section navigation (2)

change column length, is it that hard?

From: Ron Arts <ron(dot)arts(at)neonova(dot)nl>
To: pgsql-novice(at)postgresql(dot)org
Subject: change column length, is it that hard?
Date: 2005-07-30 18:37:29
Message-ID: 42EBC8E9.8010800@neonova.nl (view raw or flat)
Thread:
Lists: pgsql-novice
Hi,

I have a lot of postgresql databases running on remote locations
using identical schemas. They run 24x7.

One of the tables contains a field username character varying(16)
that needs to become varying(40), so just a little longer.

A simple 'alter table alter column ....' does not work so I tried
creating a new column, dropping the old, and renaming:

dbse=# alter table contact add column tmp_user varchar(40);
ALTER TABLE
dbse=# update contact set tmp_user = username;
UPDATE 71
dbse=# alter table contact alter column tmp_user set default '';
ALTER TABLE
dbse=# alter table contact alter column tmp_user set not NULL;
ALTER TABLE
dbse=# alter table contact drop column username
dbse-# ;
NOTICE:  rule _RETURN on view ox_deps depends on table contact column username
NOTICE:  view ox_deps depends on rule _RETURN on view ox_deps
NOTICE:  rule _RETURN on view pptpusers depends on table contact column username
NOTICE:  view pptpusers depends on rule _RETURN on view pptpusers
NOTICE:  rule _RETURN on view team_members depends on table contact column username
NOTICE:  view team_members depends on rule _RETURN on view team_members
ERROR:  cannot drop table contact column username because other objects depend on it
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

Oh man, I cannot use drop column cascade, this is a live database.

Googling led me to believe I should remove dependencies on the column,
then do my thin, and then recreate dependencies.

Can anyone show me an example how to do this? Please note I did not
design this database, and my grasp of views and rules is almost zero.

Thanks,
Ron Arts



-- 
NeoNova BV, The Netherlands
Professional internet and VoIP solutions

http://www.neonova.nl   Kruislaan 419              1098 VA Amsterdam
info: 020-5628292       servicedesk: 020-5628292   fax: 020-5628291

The following disclamer applies to this email:
http://www.neonova.nl/maildisclaimer

Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2005-07-30 20:19:45
Subject: Re: change column length, is it that hard?
Previous:From: Michael GlaesemannDate: 2005-07-30 08:33:03
Subject: Re: SQL function

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group