18

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.)

Improve this question
2
  • How did you add the column in the first place? You can't ALTER VIEW ... ADD COLUMN. Are you using CREATE OR REPLACE VIEW ? Show your code please.
    – Craig Ringer
    Sep 25, 2014 at 2:29
  • @CraigRinger, yes, 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 the VIEW has to also not return it anymore.
    – Yanick Rochon
    Sep 25, 2014 at 2:32

1 Answer 1

Reset to default
22

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.

Improve this answer
4
  • 3
    So, what you're saying is that, whenever a large application with complexe relationships needs to change a column, it is require to recreate the entire (or at least most part of the) DDL? I don't have much experience with postgre, but coming from mySQL, I've never had problems like that (with Oracle, SQL Server or MySQL), and it seems strange to me that the change cannot simply be done and errors (if any) be thrown at execution time instead. This limitation is quite restrictive.
    – Yanick Rochon
    Sep 25, 2014 at 13:54
  • @YanickRochon Yep, it's a pain, and I'd love to see it improved. If you'd like to help make that happen, consider funding work on it; see postgresql.org/support/professional_support .
    – Craig Ringer
    Sep 25, 2014 at 14:10
  • We're too small to fund such an enterprise. But glad to see it's not a fixed topic.
    – Yanick Rochon
    Sep 25, 2014 at 14:25
  • 2
    @YanickRochon Fair enough. It's on the TODO - "allow view/rule recompilation when the underlying table changes", wiki.postgresql.org/wiki/Todo#Views_and_Rules .
    – Craig Ringer
    Sep 25, 2014 at 14:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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