Some queries come back again and again: the club members, the revenue per customer. A viewviewA query saved in the database under a name. You read it like a table. saves a query under a name.

Create a view

CREATE VIEW members AS
SELECT id, name, email
FROM customers
WHERE is_member = true;

Now read the view like a table. You can filter it, sort it and join it:

SELECT name FROM members WHERE id > 4 ORDER BY name;

A view stores the query, not the rows

Each time you read a view, PostgreSQL runs its saved query on the current data.

When a customer joins the club, the customer is in the view at once. You do not refresh anything. In the lab, a view has its own tab, like a table.

Change or remove a view

CREATE OR REPLACE VIEW replaces the saved query. PostgreSQL keeps the old columns: same names, same types, same order. You can add new columns at the end only.

DROP VIEW members; removes the view. The rows in customers stay.

Why use views

• One name for a rule. "Member" means is_member = true in one place, not in twenty queries.
• Shorter queries. Hide a long join behind a name.
• Access control. Let a user read a view but not the table behind it. Chapter 15 shows how.