Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
The Postgresql documentation is clear on this point: you can not change the columns of an existing 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) (...)
Reference: http://www.postgresql.org/docs/9.3/static/sql-createview.html
Why on earth do I have to either DROP the view or ALTER it for modifying it ?
It's an implementation limitation. It's theoretically possible, of course, but nobody's written the code to handle it yet.
To cope with column removals or type changes, PostgreSQL would have to scan every view that references the view being modified (using pg_catalog.pg_depend) to see if any of them relied on the column. It'd also need to look for whole-row references and disallow changes in those cases.
It's less clear why adding a column is not permitted. Again, I suspect that's down to whole-row references. If pg_depend was checked for whole-row references without finding any, and the new column appeared at the end, it'd be OK to add it.
However, views created with SELECT * FROM wouldn't "inherit" the new column, because * gets expanded into a column-list during view creation, though. So if you had view_A that does a SELECT * FROM view_B, and you added a column to view_B, it wouldn't appear in view_A. Yet if you dropped and re-created view_A, the column would appear. Needless to say, that's not good. To cope with that, PostgreSQL would have to keep track of whether a given view's column list ("targetlist" in PostgreSQL internal terms) came from a * wildcard. Which is more complicated than you'd think because you can write someview.* too.
All in all - it's complicated, and nobody's wanted it enough to do the work to implement it.
DROP VIEW ... CASCADE, but then you have to re-create all the views that got dropped as dependencies too..
Apr 9, 2014 at 13:32
ALTER VIEWis pretty restrictive, too. If you want major changes in a view,DROPandCREATEagain is the only option.CREATE **OR REPLACE**, I expect DDL lets me change columns, because that's the main reason (at least for me) toALTERa view.