# CREATE VIEW

> Expose reusable queries through views and understand when views accept updates.

---

LLMS index: [llms.txt](/en/llms.txt)

---

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 {#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

## Example {#example}

```sql {num="48.1" caption="CREATE VIEW"}
-- 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 columns or change their types this way. Use `ALTER VIEW ... RENAME COLUMN` to rename a column; other incompatible changes may require drop and recreate.

## Run the example {#run}

To run (requires `customers_example` and `orders_example`):

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

## Common pitfalls {#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` must preserve existing column names, order, and types, even when there are no dependents. It can append columns; use `ALTER VIEW` for a rename.
- Views can hide expensive joins, so a one-liner against a view may execute as a multi-second query. Inspect with `EXPLAIN`.

> [!TIP] 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.

> [!NOTE] 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.

## Downloads {#downloads}

SQL file: [create-view.sql](/examples/source/create-view.sql) · [All lesson files](/examples/examples.zip) · [Setup guide](/en/#prerequisites)