A Query With a Name: Views

You now have a schema that’s normalized and wired together with honest keys. The catch is that a well-normalized schema is often inconvenient to query. The fact you want lives split across four tables, and the join to reassemble it is the same twelve lines every time. A view fixes that without denormalizing anything. It’s a query you give a name to, and from then on you can SELECT from that name as if it were a table. Think of a view as an interface: a stable, readable surface over the schema. The people and code using it never have to know how the tables underneath are shaped.

A view is a named SELECT

The syntax is exactly that. CREATE VIEW name AS <query>:

CREATE VIEW scratch.affordable_books AS
SELECT book_id, title, genre, price
FROM books
WHERE price < 20;

Now scratch.affordable_books behaves like a table you can query:

SELECT count(*) AS affordable_count FROM scratch.affordable_books;

 affordable_count
------------------
              174

SELECT * FROM scratch.affordable_books ORDER BY price LIMIT 3;

 book_id |     title      | genre  | price
---------+----------------+--------+-------
     218 | Book Title 218 | Sci-Fi |  5.14
     113 | Book Title 113 | Poetry |  5.19
      77 | Book Title 77  | Poetry |  5.23

Nothing was copied. The view stores no rows. It’s a saved query that runs against books every time you touch it, and you’ll see the proof of that at the end. What it bought you is a name and a boundary: callers say affordable_books and never write the price < 20 filter themselves, so the definition of “affordable” lives in one place. Change the threshold once, in the view, and every caller updates.

Views over joins and aggregates

The real payoff is hiding a complicated query behind a simple name. The per-author revenue figure needs a three-table join and a group-by. Wrap it once:

CREATE VIEW scratch.author_revenue AS
SELECT a.author_id, a.name AS author,
       count(DISTINCT oi.order_id) AS orders,
       round(sum(oi.unit_price * oi.quantity), 2) AS revenue
FROM authors a
JOIN books b        ON b.author_id = a.author_id
JOIN order_items oi ON oi.book_id = b.book_id
GROUP BY a.author_id, a.name;

And from then on, “top authors by revenue” is a one-liner anybody can write:

SELECT * FROM scratch.author_revenue ORDER BY revenue DESC LIMIT 5;

 author_id |  author   | orders |  revenue
-----------+-----------+--------+-----------
        15 | Author 15 |   5582 | 401290.12
        38 | Author 38 |   5237 | 371682.05
        11 | Author 11 |   4937 | 347047.70
        34 | Author 34 |   4967 | 326048.98
        23 | Author 23 |   4757 | 308125.44

This is the view as a contract. Application code, a dashboard, a reporting tool — they all read author_revenue and never learn how the join is built or which tables it touches. Refactor the underlying schema later, keep the view’s columns the same, and every consumer is untouched. That decoupling is why views are the front door to a database that more than one team queries.

Updatable views, and the guardrail they need

A view built from a single table, with no aggregation or DISTINCT, is often updatable. You can INSERT, UPDATE, and DELETE through it, and Postgres rewrites the change onto the base table. That’s handy, and it hides a trap. Here’s a scratch table and a view over its cheap rows:

CREATE VIEW scratch.cheap_books AS
SELECT book_id, title, price FROM scratch.books_copy
WHERE price < 20
WITH CHECK OPTION;

Inserting a row that belongs in the view works fine:

INSERT INTO scratch.cheap_books (title, price) VALUES ('Bargain', 12.00) RETURNING *;

 book_id |  title  | price
---------+---------+-------
       3 | Bargain | 12.00

Now the trap. What happens if you insert through the view a row that does not satisfy the view’s WHERE? The row would land in the base table but immediately fall outside the view — you’d insert something you can’t then see. WITH CHECK OPTION is the clause that forbids exactly this:

INSERT INTO scratch.cheap_books (title, price) VALUES ('Too Expensive', 99.00);

ERROR:  new row violates check option for view "cheap_books"
DETAIL:  Failing row contains (4, Too Expensive, 99.00).

Rejected, because 99.00 fails the view’s price < 20. To see why the clause earns its keep, drop it and run the same shape of insert through a view with no check:

CREATE VIEW scratch.cheap_nocheck AS
SELECT book_id, title, price FROM scratch.books_copy WHERE price < 20;

INSERT INTO scratch.cheap_nocheck (title, price) VALUES ('Ghost', 88.00) RETURNING *;
SELECT * FROM scratch.cheap_nocheck WHERE title = 'Ghost';

 book_id | title | price
---------+-------+-------
       5 | Ghost | 88.00        -- the RETURNING from the insert

 book_id | title | price
---------+-------+-------
(0 rows)                        -- ...but the view can't see it

The insert succeeded — the row is really in books_copy — but it’s invisible through the view, because 88.00 isn’t < 20. That’s the silent surprise WITH CHECK OPTION exists to prevent: a write that reports success and then can’t be found where you wrote it. Any view you allow writes through should almost always carry the check.

A view is a rewrite, not storage

The one idea that makes views click: a view stores nothing. When you query it, Postgres substitutes the view’s definition into your query and plans the whole thing against the base tables. You can watch it happen. Query the view with an extra filter on top:

EXPLAIN SELECT * FROM scratch.affordable_books WHERE genre = 'Sci-Fi';

 Seq Scan on books  (cost=0.00..15.00 rows=29 width=32)
   Filter: ((price < '20'::numeric) AND (genre = 'Sci-Fi'::text))

There is no affordable_books in the plan at all. The view dissolved into a scan of books, and its price < 20 merged with your genre = 'Sci-Fi' into a single filter. That’s the whole mechanism: a view is textual substitution the planner does before it optimizes. Which is why a view over an indexed table still uses the index, and why a view is never “stale” — it re-derives from live data on every read.

That last point is the sharp contrast with the materialized view from the performance series. A materialized view does store its result, on disk, and you REFRESH it on a schedule. So a plain view is always current but pays the full query cost every read; a materialized view is instant to read but only as fresh as its last refresh. Reach for the materialized kind when the underlying query is expensive and slightly-stale data is acceptable. Reach for a plain view — the default — when you want a clean, always-current interface and the query is cheap enough to run each time.

Because a view is just a rewrite, its column list is baked in at creation. If you later ALTER a base table to add a column, a SELECT * view won’t pick it up until you recreate it. Naming columns explicitly in the view, as we did, is the stable choice. (When you do need to change a view’s definition, CREATE OR REPLACE VIEW edits it in place — as long as you keep the existing columns and only add new ones at the end.)

A view is also a permission boundary

There’s a second reason views are the front door to a shared database, and it’s about access, not convenience. You can grant a user SELECT on a view while granting them nothing on the underlying tables. Because the view is a fixed query, it can expose a subset — some columns, some rows — and the user sees exactly that and no more. A support team can get a customers_public view with name and city but not email or payment details. They read from that view alone, with no direct rights to the customers table at all. The view becomes the only window onto the data, and its WHERE and column list define the window’s shape. That makes views a natural partner to the roles and row-level security we’ll get to later in the series. The view narrows what is visible; the permission system controls who gets to look through it.

Final thoughts

A view is a query with a name, and that turns out to be one of the highest-leverage tools in the schema. It gives you a stable interface over a normalized design, so callers read simple names instead of writing complicated joins, and a refactor underneath leaves them untouched. It can hide a three-table aggregate behind a one-line SELECT. It’s updatable when it’s simple enough, and WITH CHECK OPTION is the guardrail that stops you writing rows the view can’t see. And under it all, a plain view is pure rewrite: no storage, never stale, planned straight against the base tables. That’s exactly what separates it from its materialized cousin. Views reshape how a query reads. The next tools reshape the columns themselves: values a table computes for you, and types you define with their own rules.

Next: Columns that compute themselves — generated columns, DOMAINs, and enums, each shown by the value or error it produces.