Postgres by Example: CREATE VIEW

A view is a named, saved SELECT. You query it like a table, but the rows are computed on the fly every time — nothing is stored. Views are excellent for hiding complexity (a big join lives behind a short name), enforcing column-level access (grant SELECT on the view but not the underlying table), and giving applications a stable interface as the schema underneath evolves. PostgreSQL also supports materialized views, which do store rows; that is the next lesson.

What you'll learn:

• Creating and replacing views with CREATE OR REPLACE VIEW
• Updatable views and the rules they must follow
• WITH CHECK OPTION to enforce that updates stay within the view
• Granting view-level access without exposing the underlying table
• Listing and dropping views

-- A simple view: customer + their order count
CREATE OR REPLACE VIEW customer_orders AS
SELECT c.id, c.name, count(o.id) AS order_count, COALESCE(sum(o.total), 0) AS total_spent
FROM customers_example c
LEFT JOIN orders_example o ON o.customer_id = c.id
GROUP BY c.id, c.name;

-- Use it like a table
SELECT * FROM customer_orders WHERE total_spent > 0 ORDER BY total_spent DESC;

-- Simple updatable view (no joins, no aggregates, no DISTINCT)
CREATE OR REPLACE VIEW alice_orders AS
SELECT * FROM orders_example WHERE customer_id = 1;

-- You can INSERT through an updatable view
INSERT INTO alice_orders (id, customer_id, total) VALUES (10, 1, 99.00);

-- WITH CHECK OPTION: refuse INSERTs/UPDATEs that wouldn't satisfy the view's WHERE
CREATE OR REPLACE VIEW alice_orders_strict AS
SELECT * FROM orders_example WHERE customer_id = 1
WITH CHECK OPTION;

-- This would fail because customer_id = 2 is not in the view's scope
-- INSERT INTO alice_orders_strict (id, customer_id, total) VALUES (11, 2, 1.00);

-- List views
SELECT viewname, definition
FROM pg_views
WHERE schemaname = 'public'
ORDER BY viewname;

-- Drop a view
DROP VIEW IF EXISTS alice_orders;
DROP VIEW IF EXISTS alice_orders_strict;
DROP VIEW IF EXISTS customer_orders;

Views are virtual: every SELECT against the view re-runs the underlying query. There is no caching — the view is essentially a macro the planner inlines. That makes views free of staleness but also means they offer no performance benefit on their own; they are about abstraction, not speed.

Updatable views — those that map cleanly to a single underlying table without aggregates, joins, DISTINCT, or set operations — accept INSERT, UPDATE, and DELETE. The action affects the underlying table. WITH CHECK OPTION is the safety belt that prevents an UPDATE from moving a row out of the view's predicate.

CREATE OR REPLACE VIEW updates the definition if the view already exists. The column list and types must remain compatible — you cannot remove or rename columns this way; you would have to drop and recreate.

To run (requires customers_example and orders_example):

$ psql -f source/create-view.sql postgres
CREATE VIEW
 id | name  | order_count | total_spent
----+-------+-------------+-------------
  1 | Alice |           2 |       80.00
(1 row)
 ...

Common pitfalls:

• Views are not magic performance — every query inlines them. For repeated heavy work, consider a materialized view.
• Updatable view rules are strict: any aggregate, DISTINCT, GROUP BY, set operation, or multiple base tables typically makes the view non-updatable. You can write INSTEAD OF triggers to allow writes anyway, but that is advanced.
• CREATE OR REPLACE VIEW cannot change a column's name or type if the view is referenced by other objects. Drop and recreate in that case.
• Views can hide expensive joins, so a one-liner against a view may execute as a multi-second query. Inspect with EXPLAIN.

Tip: Views are an underused tool for application security. Grant the application role SELECT only on a view that returns just the columns it should see (e.g. excluding password_hash). The underlying table remains protected.

Try it: Create a view that lists customers without orders (HAVING count(o.id) = 0). Use \d customer_orders in psql to inspect a view. Then create an UPDATE-able view scoped to one customer and update through it — observe the underlying table change.

Source: create-view.sql

Next: Materialized Views

Home: Postgres by Example