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);
dbse=# update contact set tmp_user = username;
dbse=# alter table contact alter column tmp_user set default '';
dbse=# alter table contact alter column tmp_user set not NULL;
dbse=# alter table contact drop column username
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.
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:
pgsql-novice by date
|Next:||From: Tom Lane||Date: 2005-07-30 20:19:45|
|Subject: Re: change column length, is it that hard? |
|Previous:||From: Michael Glaesemann||Date: 2005-07-30 08:33:03|
|Subject: Re: SQL function|