PostgreSQL Views: How Can I Modify Columns Used in a View Without Dropping and Recreating It?

PostgreSQL: Modifying Columns Used in Views

When attempting to modify a column used in a PostgreSQL view, it is typically necessary to drop the view, make the column change, and recreate the view. This process can be tedious and time-consuming. Is there a way to alleviate this burden?

Solution

Permanent Resolution:

Avoid using character varying(n) data types with a defined length. Instead, use the text or varchar data type without a length specifier. If necessary, enforce a maximum length using a CHECK constraint. This approach eliminates the need to modify the view when altering column types.

CREATE TABLE monkey (name text NOT NULL);

ALTER TABLE monkey ADD CONSTRAINT monkey_name_len CHECK (length(name) < 101);

Detailed Explanation

PostgreSQL views are not simply aliases to subqueries, but rather specialised tables with their own rule structure. As a result, when modifying underlying tables, it is often necessary to modify dependent views as well. The ALTER VIEW statement can change view properties, but not the underlying query.

To change the query, use CREATE OR REPLACE VIEW. However, this operation is not possible when modifying resulting column data types. In such cases, the old view must be dropped and a new one created. This action does not delete any data but may remove any additional attributes associated with the view.

The above is the detailed content of PostgreSQL Views: How Can I Modify Columns Used in a View Without Dropping and Recreating It?. For more information, please follow other related articles on the PHP Chinese website!