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.