PostgreSQL : drop column from view

I have a VIEW where I'm attempting to create an evolution script for, so I can add a column to it. That part works fine; column added just fine. However, the reverse does not work; remove that last added column fails with a ERROR: cannot drop columns from view message. The problem is that this particular view has many references, both from and to, therefore I cannot just DROP CASCADE the darn thing!

Is there a reason why I cannot remove a newly added column from a given VIEW? Then, what can I do to accomplish this task?

(Note: the circumstances, here, are what they are, but I can very well see a similar situation, aka dropping a column from a view, in many other cases.)

PostgreSQL (true up to at least 9.4) doesn't currently support removing a column with CREATE OR REPLACE VIEW.


The new query must generate the same columns that were generated by the existing view query (that is, the same column names in the same order and with the same data types), but it may add additional columns to the end of the list.

There is no fundamental reason why support for dropping columns couldn't be added, but nobody has done the work required to implement it yet.

CREATE OR REPLACE VIEW would have to recursively scan all dependencies and make sure that none of them referenced the column to be dropped. If they used SELECT it'd have to remove the column from the expansion of in the dependency then scan its dependencies too. There's a fair bit of work involved in doing that, and there are some areas where it's not clear how exactly dropping the column should behave especially when it comes to interactions with dump and reload. So nobody's wanted the feature enough to implement it yet. Patches and/or sponsorship of development are welcome.

You will have to drop the view and everything that depends on it, then re-create it and its dependencies. (The same used to be true for adding a column to a view; support for adding columns was introduced in 8.4).

Note that in general there's no expectation that DDL be reversible. The concept of "devolutions" is really flawed. For example, if you drop a column, then add it again, the data is still gone.

StackExchange Database Administrators Q#77564, answer score: 23