When you want to change a view in Postgres, for instance to add an extra field, it can happen that the view is used elsewhere (perhaps in another view) and that it can't be changed. Instead, the depending views first have to be dropped. This can easily cascade, and you might even end up having to drop all views and recreate them. This is a lot of tricky work and very error prone.

I've come up with a bare bones way around this, and basically comes down to:

  1. dump the database
  2. change the view definition in the SQL file 
  3. drop the database
  4. recreate the database from the altered SQL file

This might sound draconic, but it is trivial to do and not error prone at all. The only downside is that the database in unavailable for a small amount of time. In our case this was not a problem.

Follow these steps:

You should test this of course by restoring to another database to make sure the patch is working OK. If you feel comfortable you could script everything together to minimise downtime.