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.
ALTER VIEW ... ADD COLUMN
. Are you usingCREATE OR REPLACE VIEW
? Show your code please.CREATE OR REPLACE VIEW
with the same def, except an extra column (because a ref'ed table has a new column added, so the view has to include it). The "devolution" removes the column from the ref'ed table, so theVIEW
has to also not return it anymore.