Take the 2-minute tour ×
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's 100% free, no registration required.

I have a materialized view in Postgres 9.3 that I'd like to update with new columns. Other materialized views also depend upon this view, but the error message indicates that dropping a view isn't possible when other objects depend on it. It also appears from the documentation that the REPLACE keyword isn't valid for a materialized view. Is there any shortcut aside from dropping all dependent objects and rebuilding each one?

ERROR: cannot drop materialized view latest_charges because other objects depend on it

share|improve this question
5  
Sadly, I think you're stuck with dropping them all and rebuilding. –  Craig Ringer Jan 7 '14 at 22:58
    
@CraigRinger interested in adding this as an answer? –  dezso Sep 1 '14 at 12:54

1 Answer 1

up vote 2 down vote accepted

As of PostgreSQL 9.4: Different to the documentation of CREATE VIEW, the documentation of CREATE MATERIALIZED VIEW does NOT mention the REPLACE keyword. There seems to be no shortcut aside from dropping all dependent objects and rebuilding each one.

When you do so, I can only recommend two small things:

  1. Use DROP MATERIALIZED VIEW blabla CASCADE to get a list of all dependent objects
  2. Do the drop and recreation of all dependent object in one transaction.
share|improve this answer
    
Thanks, this is exactly how I've been doing it. It's just a pain to keep straight since I'm building one base materialized view for analytical purposes that gets reused in many other views. That base view rarely changes, but the ones that depend on it change daily. –  John May 19 at 17:58

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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