How to ALTER a view in PostgreSQL

Question

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:

Drop all the dependent objects,
Alter the view,
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?

Frank Heikens · Accepted Answer · 2010-08-29 08:45:08Z

6

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

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;
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.
Make all changes within a single transaction.

answered Aug 29, 2010 at 8:45

Frank Heikens

129k26 gold badges157 silver badges153 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

mevdiven Over a year ago

Adding a column is a problem when view has dependent objects. Try this; CREATE TABLE one ( col_a INT, col_b VARCHAR(2), col_c DATE ); CREATE OR REPLACE view vw_one AS SELECT col_a, col_b FROM one; CREATE OR REPLACE VIEW vw_two AS SELECT col_a, col_b FROM vw_one; CREATE OR REPLACE VIEW vw_one AS SELECT col_a, col_b, col_c FROM one; Am I missing something?

Frank Heikens Over a year ago

It works fine over here, 9.0beta4. I don't have older versions at my dev machine, but it should work in 8.4 as well. What error message do you get?

mevdiven Over a year ago

They might have fixed on version 9.0. I saw couple of bug fix request in postgreSQL community. Here's the error I'm getting. ERROR: cannot change number of columns in view SQL state: 42P16

Frank Heikens Over a year ago

And what version do you have? SELECT version();

Frank Heikens Over a year ago

From the 8.4 release notes: Allow CREATE OR REPLACE VIEW to add columns to the end of a view (Robert Haas)

hartmut27 · Accepted Answer · 2014-08-14 17:23:10Z

2

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.

answered Aug 14, 2014 at 17:23

hartmut27

6,3695 gold badges42 silver badges45 bronze badges

1 Comment

cloudsurfin Over a year ago

Thanks. I did the opposite (removed 'drop view', added 'or replace') and it worked great.

Collectives™ on Stack Overflow

How to ALTER a view in PostgreSQL

2 Answers 2

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related