3

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 ?

  • ALTER VIEW is pretty restrictive, too. If you want major changes in a view, DROP and CREATE again is the only option. – ypercubeᵀᴹ Apr 9 '14 at 11:04
  • It's a good thing that PostgreSQL's DDL commands are transactional! – Trygve Laugstøl Apr 9 '14 at 11:47
  • Are you coming from an Oracle background? Postgresql does things differently! – Colin 't Hart Apr 9 '14 at 12:04
  • @Colin'tHart Yes, I am – Stephan Apr 9 '14 at 13:19
  • It's not only about Postgres doing things in a different way, is that Postgres changes the meaning of words. If syntax says CREATE **OR REPLACE**, I expect DDL lets me change columns, because that's the main reason (at least for me) to ALTERa view. – EAmez Jul 3 at 12:38
7

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.

  • 1
    At least from 8.4+, it's possible to "add additional columns to the end of the list". – Stephan Apr 9 '14 at 13:22
  • Would it be possible to tell Postgresql to perform a DROP VIEW followed by a CREATE VIEW when he parses a CREATE OR REPLACE VIEW ? – Stephan Apr 9 '14 at 13:26
  • 2
    @Alex No, because that'd require dropping any dependent views. You can DROP VIEW ... CASCADE, but then you have to re-create all the views that got dropped as dependencies too.. – Craig Ringer Apr 9 '14 at 13:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.