Considerations for PostgreSQL Update View scripts

The project was originally based on Oracle, and after porting to PostgreSQL, the script that created/updated the view followed the Oracle-style create or REPLACE view form, with minimal modification. However, whenever the view definition is to be updated, it is often reported as "Cannot change name of view column XXX to yyy" error, which usually occurs when the view modifies a field name, adds a field in the middle, and deletes a field.

The reason for this is that PostgreSQL supports the Create OR REPLACE view semantics, but has an important limitation that is easy to overlook (Oracle does not have this limitation), and its official documentation describes:

That is, the update view can only add fields at the end, cannot change field names, cannot delete fields, and cannot add fields in the middle, which is intolerable during the project development phase. Although PostgreSQL provides the ALTER VIEW statement, it is not as intuitive as it is directly placed in CREATE view.

Therefore, it is recommended that the script discard the form of the Oracle-style create OR REPLACE view instead of the MySQL-style drop view and create view. However, if there is a hierarchical reference relationship between views, such as view A is based on view B, then create must first build B and then A,drop must first delete A and then delete B. The order of adjustment is troublesome when the hierarchy is referenced more or more frequently.

To reduce complexity, the script ultimately only considers the order of CREATE view, and when you drop view, use the IF EXISTS and Cascade options as follows:

DROP VIEW IF EXISTS CASCADE ; CREATE VIEW  as ...; DROP VIEW IF EXISTS CASCADE ; CREATE VIEW  as ...;

Considerations for PostgreSQL Update View scripts