The essentials
Quick reference
One focused task per row. Jump to the related section for complete, working examples.
| Use | Syntax | Examples |
|---|---|---|
| Create a view | CREATE VIEW active_users AS
SELECT id, name
FROM users
WHERE active; | View examples |
| Replace a compatible view | CREATE OR REPLACE VIEW active_users AS
SELECT id, name
FROM users
WHERE active; | View examples |
| Enforce view predicate | CREATE VIEW open_items AS
SELECT *
FROM items
WHERE status = 'open'
WITH LOCAL CHECK OPTION; | View examples |
| Create a security barrier | CREATE VIEW safe_users
WITH (security_barrier = true) AS
SELECT id, name
FROM users; | View examples |
| Grant view access | GRANT SELECT ON active_users TO reporting_role; | View examples |
| Create stored results | CREATE MATERIALIZED VIEW sales_summary AS
SELECT day, sum(total)
FROM sales
GROUP BY day; | View examples |
| Create unpopulated | CREATE MATERIALIZED VIEW sales_summary AS
SELECT ...
WITH NO DATA; | View examples |
| Refresh completely | REFRESH MATERIALIZED VIEW sales_summary; | View examples |
| Refresh while readable | REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary; | View examples |
| Index stored results | CREATE UNIQUE INDEX sales_summary_day_uidx
ON sales_summary (day); | View examples |
| Inspect a view definition | SELECT pg_get_viewdef('active_users'::regclass, true); | View examples |
| Drop without cascading | DROP VIEW active_users RESTRICT; | View examples |
A view stores a query and reads current underlying data; a materialized view stores query results and must be refreshed. Use views as stable query and security interfaces, materialized views for measured read performance when staleness is acceptable, and document ownership, permissions, dependencies, and refresh cadence.
Step by step
Detailed examples
Use views as query interfaces
A normal view stores no rows; its defining query is integrated into statements that reference it. Explicit column lists protect consumers from accidental shape changes. CREATE OR REPLACE cannot arbitrarily rename, reorder, or change existing output types, so breaking API changes need a migration.
CREATE VIEW api_active_users (user_id, display_name) AS
SELECT id, name
FROM users
WHERE active;Understand when writes pass through
Simple single-table views can be automatically updatable; aggregates, grouping, set operations, and many other constructs are not. CHECK OPTION restricts inserts and updates through an updatable view to rows visible through its predicate. Triggers or rules can implement deliberate write behavior for complex views.
CREATE VIEW open_items AS
SELECT item_id, title, status
FROM items
WHERE status = 'open'
WITH LOCAL CHECK OPTION;Treat views as security objects with owners
Views can expose selected columns and rows while callers receive privileges only on the view. Security invoker and barrier options affect whose permissions and row-security policies apply and how predicates may be reordered. Use leakproof trusted functions and test with the actual caller role.
CREATE VIEW reporting_users WITH (security_barrier = true) AS
SELECT user_id, created_at
FROM users
WHERE deleted_at IS NULL;
GRANT SELECT ON reporting_users TO reporting_role;Make freshness an explicit contract
A materialized view stores rows and can be queried and indexed like a relation, but it cannot be updated directly. WITH NO DATA delays population and leaves it unscannable. Record the last successful refresh externally or in a companion table so consumers can assess staleness.
CREATE MATERIALIZED VIEW sales_summary AS
SELECT sold_at::date AS day, SUM(total) AS revenue
FROM sales
GROUP BY sold_at::date
WITH NO DATA;Choose refresh availability and index requirements
A regular refresh generally uses fewer resources but can block readers. CONCURRENTLY preserves reads and requires a populated materialized view with a unique, non-partial, non-expression index covering all rows. Only one refresh may run on a materialized view at a time, and output order is never guaranteed without query ORDER BY.
REFRESH MATERIALIZED VIEW sales_summary;
CREATE UNIQUE INDEX sales_summary_day_uidx ON sales_summary (day);
REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary;Inspect dependencies before replacement or removal
Views depend on referenced relations, functions, and types, and downstream views may depend on them. Prefer RESTRICT so an unexpected dependency blocks removal; CASCADE can delete a large graph. Capture definitions and grants before migrations.
SELECT pg_get_viewdef('api_active_users'::regclass, true);
DROP VIEW api_active_users RESTRICT;Sources and further reading
References
Authoritative documentation used to verify and expand this cheat sheet.
- PostgreSQL Global Development GroupPostgreSQL: CREATE VIEWpostgresql.org
- PostgreSQL Global Development GroupPostgreSQL: CREATE MATERIALIZED VIEWpostgresql.org
- PostgreSQL Global Development GroupPostgreSQL: REFRESH MATERIALIZED VIEWpostgresql.org
- PostgreSQL Global Development GroupPostgreSQL: Materialized Viewspostgresql.org
Help us improve
Found a typo or missing example?
Tell us what would make this cheat sheet clearer, more complete, or more useful.