Alter column ignoring dependent views

Question

I have column column with character varying(20) type, i want to increase it to 50

ALTER TABLE table ALTER COLUMN column TYPE character varying(50);

I get an error that view view_name depends on column "column". I wonder how can i alter column without dropping and recreating about 10 depending views?

Answer 1

The answer to your question can be found on this blog

PostgreSQL is very restrictive when it comes to modifying existing objects. Very often when you try to ALTER TABLE or REPLACE VIEW it tells you that you cannot do it, because there's another object (typically a view or materialized view), which depends on the one you want to modify. It seems that the only solution is to DROP dependent objects, make desired changes to the target object and then recreate dropped objects.

It is tedious and cumbersome, because those dependent objects can have further dependencies, which also may have other dependencies and so on. I created utility functions which can help in such situations.

The usage is very simple - you just have to call:

select deps_save_and_drop_dependencies(p_schema_name, p_object_name);

You have to pass two arguments: the name of the schema and the name of the object in that schema. This object can be a table, a view or a materialized view. The function will drop all views and materialized views dependent on p_schema_name.p_object_name and save DDL which restores them in a helper table.

When you want to restore those dropped objects (for example when you are done modyfing p_schema_name.p_object_name), you just need to make another simple call:

select deps_restore_dependencies(p_schema_name,p_object_name);

and the dropped objects will be recreated.

These functions take care about:

• dependencies hierarchy
• proper order of dropping and creating views/materialized views across hierarchy
• restoring comments and grants on views/materialized views

Click here for a working sqlfiddle example or check this gist for a complete source code