In PostgreSQL, what happens to a view defined as SELECT * FROM a table when a column of that table is later added, retyped or dropped?
answer
- star expanded once
- stored as a rule
- _RETURN in pg_rewrite
- append-only CREATE OR REPLACE
basics
~20 sPostgreSQL expands * at creation and stores the parsed query as the view's _RETURN rule, so a new table column stays invisible until the view is replaced, and retyping or dropping a column it uses fails.
solid answer
~40 sA PostgreSQL view is a relation without storage plus an `ON SELECT DO INSTEAD` rule, conventionally named `_RETURN`, kept in `pg_rewrite`. The parser expands `*` into the individual columns at creation, and PostgreSQL records a dependency on each column the view uses. So an `ADD COLUMN` on the table is not reflected; you need `CREATE OR REPLACE VIEW`, which may only append columns at the end. `ALTER COLUMN ... TYPE` on a used column fails with `cannot alter type of a column used by a view or rule`, and `DROP COLUMN` fails on the dependency unless you add `CASCADE`, which drops the view. Renaming the table column is harmless because the rule refers to it by position, not by name. `pg_get_viewdef()` shows the expanded column list.
code
sql · 12 linesCREATE VIEW support.customer_list AS
SELECT * FROM crm.customers;
ALTER TABLE crm.customers ADD COLUMN phone text;
SELECT pg_get_viewdef('support.customer_list'::regclass, true);
-- still lists only the original columns; phone is missing
ALTER TABLE crm.customers ALTER COLUMN email TYPE varchar(320);
-- ERROR: cannot alter type of a column used by a view or rule
CREATE OR REPLACE VIEW support.customer_list AS
SELECT * FROM crm.customers; -- appends phone at the endgo deeper
Recall that * in a PostgreSQL view is expanded once at creation, so new table columns do not appear on their own.
Explain the _RETURN rule in pg_rewrite and why that makes retyping or dropping a referenced column fail while renaming works.
Plan migrations around view dependencies: capture definitions and grants, change everything in one transaction, and avoid blind CASCADE.
Set a team convention, such as explicit column lists and views managed as migration objects, so column changes never surprise a release.
## How PostgreSQL stores a view In PostgreSQL a view is a relation in `pg_class` with no storage of its own, plus a rewrite rule. The rule is an `ON SELECT DO INSTEAD` rule, conventionally named **`_RETURN`**, stored in the `pg_rewrite` catalog (`ev_type` `1` means `SELECT`). When a query mentions the view, the rewriter replaces the reference with the rule's query tree before planning. Two consequences follow: - The stored definition is a **parsed query tree**, not the SQL text you typed. `pg_get_viewdef()` and the `definition` column of `pg_views` reconstruct text from it. - The parser **expands `*`** into the individual column list before the rule is stored, so the rule system never sees a star. Materialized views carry a `_RETURN` rule too, but there it is used only to repopulate the stored data on refresh. ## What each table change does Take `CREATE VIEW support.customer_list AS SELECT * FROM crm.customers;` and then alter the table: | Change on `crm.customers` | Effect on the view | |---|---| | `ADD COLUMN phone text` | View keeps its old column list; `phone` is not visible | | `RENAME COLUMN email TO email_address` | View keeps working; its own column stays named `email` | | `ALTER COLUMN email TYPE varchar(320)` | Error: `cannot alter type of a column used by a view or rule` | | `DROP COLUMN email` | Error: other objects depend on it, unless `CASCADE` drops the view | The rename case works because the rule references columns by their column number in the table, not by name; `pg_get_viewdef()` afterwards shows the new name aliased back to `email`. ## Why PostgreSQL binds a view this early Binding the query at creation is a deliberate trade: - **The view cannot silently break at query time.** Every table, column, function and operator it uses is resolved once, and each is recorded as a dependency in `pg_depend`, so a change that would invalidate the view is refused up front instead of surfacing as an error in a report hours later. - **Name changes are safe.** Because references are by identity, renaming a table, schema or column does not disturb the view. - **The price is migration friction.** A column type change or drop now involves two objects, and a change that cascades through stacked views involves more. ## The rules for CREATE OR REPLACE VIEW `CREATE OR REPLACE VIEW` swaps the `_RETURN` rule but must keep the existing columns compatible, because other views and rules may already depend on them: 1. Existing columns must keep the **same names, in the same order, with the same data types**. 2. New columns may be **appended at the end** only. 3. A shorter list fails with `cannot drop columns from view`. 4. A changed name fails with `cannot change name of view column ...`, whose hint points to `ALTER VIEW ... RENAME COLUMN`. 5. A changed type fails with `cannot change data type of view column ...`. It keeps ownership, grants and any non-`SELECT` rules, which is why it is preferred over drop-and-create whenever it is allowed. It does **replace** the view's `WITH (...)` options and check option with whatever the new statement says, so leaving the clause out resets them. ## A safe workflow for a breaking table change When a migration must retype or drop a column a view uses: 1. Capture the definition with `pg_get_viewdef('support.customer_list'::regclass, true)` and note its grants. 2. In **one transaction** (PostgreSQL DDL is transactional), `DROP VIEW`, alter the table, then recreate the view with an explicit column list. 3. Re-issue the grants, since dropping the view discarded them, and recreate any views stacked on top of it. Dependent views multiply the work, so list them first rather than reaching for `DROP ... CASCADE`, which drops every dependent view along with it. ## Practical guidance - Prefer an **explicit column list** over `SELECT *` in views: the star buys nothing, because it is frozen at creation anyway, and it hides which columns the view really exposes. - After an `ADD COLUMN`, re-running `CREATE OR REPLACE VIEW` with the same `SELECT *` picks the new column up, because `ADD COLUMN` puts it last and the replacement may append. - Treat views as schema objects in migrations; their dependencies make column changes on busy tables a two-object change.
- Why does PostgreSQL refuse CREATE OR REPLACE VIEW when you only reorder two existing columns?Other views and rules may reference this view's columns by position and type, so replacing the definition must keep every existing column's name, order and type. Only appending columns at the end is allowed; a reorder looks like a rename to the check and fails with `cannot change name of view column`.
- Where would you look in PostgreSQL to see the stored definition of a view?The rule lives in `pg_rewrite` as the `_RETURN` rule for the view's `pg_class` row, but you read it as text through `pg_get_viewdef()`, the `definition` column of `pg_views`, or psql's `\d+`. All of them reconstruct SQL from the stored query tree, with `*` already expanded.
- A SELECT * view automatically shows columns added to the table later.
- PostgreSQL stores a view as the original SQL text and re-parses it on each query.
- CREATE OR REPLACE VIEW can rename, retype or remove existing columns.
- Renaming a table column breaks every view that selects it.
- DROP COLUMN ... CASCADE only removes the column from dependent views.