Posgres SQL Views

 Strong views on PostgreSQL VIEWs | boringSQL

Quick Summary

The core message is that VIEWs are rewrite rules (macros), not tables. Because Postgres freezes the view's definition (including column types and OIDs) at the time of creation, any structural change to the underlying tables—like dropping a column or changing a data type—is often blocked or requires a full manual recreation of the entire view dependency chain.

---

Key Points

• Views are Macros: When you query a view, the PostgreSQL rewriter literally pastes the view's SQL into your query. This makes simple views "free" in terms of performance because the planner can optimize the whole thing at once.

• The Dependency Trap: * Columns are referenced by internal ID numbers, not names.

  • Renaming a column works, but dropping or changing a type is blocked because the view's stored parse tree is "frozen."

  • Using SELECT * in a view is a "trap" because it expands to a static list of columns at creation time and won't pick up new table columns automatically.

• Nested View Spirals: Deeply nested views (views on top of views) create a "black box" that hides performance issues. You cannot truly know how a query will perform without unrolling every layer of the view tree.

• Maintenance Headaches: * Using CASCADE to force a schema change is dangerous—it drops the views and deletes all associated GRANTS and RLS policies with no undo.

  • The standard fix is a manual "drop-alter-recreate" cycle, which is tedious for complex dependency trees.

• Safety via Transactional DDL: Unlike Oracle or SQL Server, Postgres allows you to wrap the entire "drop and recreate" sequence in a single BEGIN/COMMIT block, ensuring that if the recreation fails, the database rolls back to its original state.

• Best Practices:

  • Always use explicit column lists instead of SELECT *.

  • Keep dependency trees shallow.

  • Map your dependencies using pg_depend before attempting migrations.