CODEWITHSUNDEEP
sqldatabasepostgresql
mevdiven
mevdiven

Reputation: 1902

How to ALTER a view in PostgreSQL

PostgreSQL does not allow altering a view (i.e. adding column, changing column orders, adding criterie etc.) if it has dependent objects. This is really getting annoying since you have to write a script to:

  1. Drop all the dependent objects,
  2. Alter the view,
  3. Recreate all the dependent objects back again.

I understand that postgreSQL developers have very reasonable concerns to prevent altering views. But do you guys have any scripts/shot-cuts to do all those manual stuff in a single run?

Upvotes: 14

Views: 30252

Answers (2)

Hartmut Pfarr
Hartmut Pfarr

Reputation: 6149

If I place a addtional "drop view xyz; commit;" before the "create or replace view xyz as ..." statement, at least in many cases I resolve the blocking problem described above.

Upvotes: 2

Frank Heikens
Frank Heikens

Reputation: 127576

Adding new columns isn't a problem, changing datatypes or changing the order of the columns, that's where you get problems.

  1. Don't change the order, it's not that important anyway, just change your query:

    SELECT a, b FROM view_name;

    SELECT b, a FROM view_name;

  2. When you have to change a datatype of a column, you have to check the depend objects as well. These might have problems with this new datatype. Just get the definition of this object and recreate after the changes. The information_schema and pg_catalog help you out.

  3. Make all changes within a single transaction.

Upvotes: 6

Related Questions