11.1 Views & Materialized Views — The Complete SQL Course

Views let you name a query so the rest of your system can read from it like a table. Materialized views go one step further: they store the query's result, then let you refresh that cached result on your schedule.

This lesson packages the design work from Section 10 into database objects. The source tables stay normalized; the view becomes the readable surface area.

CREATE VIEW: name the query

A view is a saved SELECT. It does not copy rows by default. When you query the view, PostgreSQL runs the stored query against the current source tables.

Here is a small cafe schema with tickets and lines. The first view hides the join and ticket-total math. The second view is the reporting API: one row per branch per day.

CREATE TABLE cafe_branches (
  branch_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  branch_name text NOT NULL UNIQUE
);
 
CREATE TABLE cafe_tickets (
  ticket_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  branch_id integer NOT NULL REFERENCES cafe_branches(branch_id),
  opened_at timestamptz NOT NULL,
  status text NOT NULL CHECK (status IN ('paid', 'void'))
);
 
CREATE TABLE ticket_lines (
  ticket_id integer NOT NULL REFERENCES cafe_tickets(ticket_id),
  line_no integer NOT NULL,
  item_name text NOT NULL,
  quantity integer NOT NULL CHECK (quantity > 0),
  charged_unit_price numeric(5,2) NOT NULL CHECK (charged_unit_price >= 0),
  PRIMARY KEY (ticket_id, line_no)
);
 
INSERT INTO cafe_branches (branch_name)
VALUES ('center'), ('harbor');
 
INSERT INTO cafe_tickets (branch_id, opened_at, status)
VALUES
  (1, TIMESTAMPTZ '2026-07-01 08:10:00+00', 'paid'),
  (1, TIMESTAMPTZ '2026-07-01 09:30:00+00', 'paid'),
  (2, TIMESTAMPTZ '2026-07-01 10:05:00+00', 'paid'),
  (2, TIMESTAMPTZ '2026-07-02 08:45:00+00', 'void');
 
INSERT INTO ticket_lines (ticket_id, line_no, item_name, quantity, charged_unit_price)
VALUES
  (1, 1, 'Latte', 2, 4.50),
  (1, 2, 'Cookie', 1, 3.00),
  (2, 1, 'Cold Brew', 1, 4.75),
  (3, 1, 'Latte', 1, 4.50),
  (3, 2, 'Cookie', 2, 3.00),
  (4, 1, 'Cold Brew', 2, 4.75);
 
CREATE VIEW paid_ticket_totals AS
SELECT ct.ticket_id,
       cb.branch_name,
       CAST(ct.opened_at AS date) AS sales_day,
       sum(tl.quantity * tl.charged_unit_price) AS ticket_total
FROM cafe_tickets AS ct
JOIN cafe_branches AS cb
  ON cb.branch_id = ct.branch_id
JOIN ticket_lines AS tl
  ON tl.ticket_id = ct.ticket_id
WHERE ct.status = 'paid'
GROUP BY ct.ticket_id, cb.branch_name, CAST(ct.opened_at AS date);
 
CREATE VIEW branch_day_sales AS
SELECT branch_name,
       sales_day,
       count(*) AS ticket_count,
       sum(ticket_total) AS gross_sales
FROM paid_ticket_totals
GROUP BY branch_name, sales_day;
 
SELECT branch_name, sales_day, ticket_count, gross_sales
FROM branch_day_sales
ORDER BY sales_day, branch_name;

CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 2
INSERT 0 4
INSERT 0 6
CREATE VIEW
CREATE VIEW
 branch_name | sales_day  | ticket_count | gross_sales
-------------+------------+--------------+-------------
 center      | 2026-07-01 |            2 |       16.75
 harbor      | 2026-07-01 |            1 |       10.50
(2 rows)

Watch the schema bar after CREATE VIEW: these objects appear as view, not as tables. That is honest. A normal view is a named query boundary, not stored data.

Views as an API layer

The view lets a report ask a business-shaped question:

SELECT branch_name, sales_day, ticket_count, gross_sales
FROM branch_day_sales
ORDER BY sales_day, branch_name;

The report does not need to know that void tickets are excluded, that ticket totals come from line quantities, or that branch names live in a separate table. That is the API layer idea: expose the columns your consumers should depend on, and keep the join machinery behind the boundary.

This is especially useful during schema evolution. You might split a table, rename an internal column, or add a better constraint, while preserving the view's outward column names for application code. A view is not magic insulation against every change — dropping a column the view needs will still break it — but it gives you a deliberate contract surface instead of letting every report reach into every table.

Materialized views: cached query results

A materialized view stores the result of its query. That makes reads cheap and predictable, but it also creates the same maintenance contract Section 10 warned about: cached data can be stale.

CREATE MATERIALIZED VIEW branch_day_sales_cache AS
SELECT branch_name,
       sales_day,
       ticket_count,
       gross_sales
FROM branch_day_sales;
 
INSERT INTO cafe_tickets (branch_id, opened_at, status)
VALUES (1, TIMESTAMPTZ '2026-07-01 11:20:00+00', 'paid');
 
INSERT INTO ticket_lines (ticket_id, line_no, item_name, quantity, charged_unit_price)
VALUES (5, 1, 'Flat White', 1, 4.25);
 
SELECT 'live view' AS source,
       branch_name,
       sales_day,
       ticket_count,
       gross_sales
FROM branch_day_sales
WHERE branch_name = 'center'
UNION ALL
SELECT 'materialized view' AS source,
       branch_name,
       sales_day,
       ticket_count,
       gross_sales
FROM branch_day_sales_cache
WHERE branch_name = 'center'
ORDER BY source;
 
REFRESH MATERIALIZED VIEW branch_day_sales_cache;
 
SELECT branch_name, sales_day, ticket_count, gross_sales
FROM branch_day_sales_cache
ORDER BY sales_day, branch_name;

CREATE MATERIALIZED VIEW
INSERT 0 1
INSERT 0 1
      source       | branch_name | sales_day  | ticket_count | gross_sales
-------------------+-------------+------------+--------------+-------------
 live view         | center      | 2026-07-01 |            3 |       21.00
 materialized view | center      | 2026-07-01 |            2 |       16.75
(2 rows)
REFRESH MATERIALIZED VIEW
 branch_name | sales_day  | ticket_count | gross_sales
-------------+------------+--------------+-------------
 center      | 2026-07-01 |            3 |       21.00
 harbor      | 2026-07-01 |            1 |       10.50
(2 rows)

That side-by-side grid is the whole trade. The live view reflects the new ticket immediately. The materialized view keeps returning the old cache until REFRESH MATERIALIZED VIEW rebuilds it.

Refresh strategies and CONCURRENTLY

Plain REFRESH MATERIALIZED VIEW rebuilds the stored result. In production, that can make readers wait while the refresh replaces the contents. PostgreSQL also supports REFRESH MATERIALIZED VIEW CONCURRENTLY, which keeps the old contents readable while building the new contents.

There is a catch: PostgreSQL needs a qualifying unique index on the materialized view so it can match old rows to new rows during the concurrent refresh. The index must cover all rows, use only column names, and not be partial; here, branch plus day is the cache's grain.

CREATE UNIQUE INDEX branch_day_sales_cache_key
  ON branch_day_sales_cache (branch_name, sales_day);
 
INSERT INTO cafe_tickets (branch_id, opened_at, status)
VALUES (2, TIMESTAMPTZ '2026-07-01 12:10:00+00', 'paid');
 
INSERT INTO ticket_lines (ticket_id, line_no, item_name, quantity, charged_unit_price)
VALUES (6, 1, 'Cookie', 1, 3.00);
 
REFRESH MATERIALIZED VIEW CONCURRENTLY branch_day_sales_cache;
 
SELECT branch_name, sales_day, ticket_count, gross_sales
FROM branch_day_sales_cache
ORDER BY sales_day, branch_name;

CREATE INDEX
INSERT 0 1
INSERT 0 1
REFRESH MATERIALIZED VIEW
 branch_name | sales_day  | ticket_count | gross_sales
-------------+------------+--------------+-------------
 center      | 2026-07-01 |            3 |       21.00
 harbor      | 2026-07-01 |            2 |       13.50
(2 rows)

The lab proves the syntax and the result. It cannot prove the production benefit because that requires another session reading while the refresh runs. The production rule is structural: create the materialized view, add a qualifying unique index, refresh it on the schedule your staleness budget allows, and measure whether the cache is worth its maintenance.

Updatable views, sharp edges included

Some simple views are updatable. A single-table view without grouping or joins can often accept INSERT, UPDATE, and DELETE as if you were writing to the base table. Complex views like branch_day_sales are read surfaces.

The sharp edge is that a view's WHERE clause is not automatically a write rule. This insert goes through the active_badges view, lands in the base table, and then disappears from the view because it does not match active = true.

CREATE TABLE staff_badges (
  badge_id integer PRIMARY KEY,
  staff_name text NOT NULL,
  active boolean NOT NULL
);
 
INSERT INTO staff_badges (badge_id, staff_name, active)
VALUES
  (1, 'Rosa', true),
  (2, 'Ken', true);
 
CREATE VIEW active_badges AS
SELECT badge_id, staff_name, active
FROM staff_badges
WHERE active = true;
 
INSERT INTO active_badges (badge_id, staff_name, active)
VALUES (3, 'Nia', false);
 
SELECT badge_id, staff_name, active
FROM staff_badges
ORDER BY badge_id;
 
SELECT badge_id, staff_name, active
FROM active_badges
ORDER BY badge_id;

CREATE TABLE
INSERT 0 2
CREATE VIEW
INSERT 0 1
 badge_id | staff_name | active
----------+------------+--------
        1 | Rosa       | t
        2 | Ken        | t
        3 | Nia        | f
(3 rows)
 badge_id | staff_name | active
----------+------------+--------
        1 | Rosa       | t
        2 | Ken        | t
(2 rows)

If you do write through a filtered view, add WITH CHECK OPTION when rows outside the view predicate should be rejected:

CREATE VIEW checked_active_badges AS
SELECT badge_id, staff_name, active
FROM staff_badges
WHERE active = true
WITH LOCAL CHECK OPTION;
 
INSERT INTO checked_active_badges (badge_id, staff_name, active)
VALUES (4, 'Mina', false);

ERROR:  new row violates check option for view "checked_active_badges"
DETAIL:  Failing row contains (4, Mina, f).

For beginners, the practical rule is simple: use views first as readable API layers. Treat writes through views as an advanced design decision, and prefer writing to the base tables unless the team has deliberately documented the view's write contract.

sql — playgroundlive

CREATE TABLE cafe_branches (
branch_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
branch_name text NOT NULL UNIQUE
);

CREATE TABLE cafe_tickets (
ticket_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
branch_id integer NOT NULL REFERENCES cafe_branches(branch_id),
opened_at timestamptz NOT NULL,
status text NOT NULL CHECK (status IN ('paid', 'void'))
);

CREATE TABLE ticket_lines (
ticket_id integer NOT NULL REFERENCES cafe_tickets(ticket_id),
line_no integer NOT NULL,
item_name text NOT NULL,
quantity integer NOT NULL CHECK (quantity > 0),
charged_unit_price numeric(5,2) NOT NULL CHECK (charged_unit_price >= 0),
PRIMARY KEY (ticket_id, line_no)
);

INSERT INTO cafe_branches (branch_name)
VALUES ('center'), ('harbor');

INSERT INTO cafe_tickets (branch_id, opened_at, status)
VALUES
(1, TIMESTAMPTZ '2026-07-01 08:10:00+00', 'paid'),
(1, TIMESTAMPTZ '2026-07-01 09:30:00+00', 'paid'),
(2, TIMESTAMPTZ '2026-07-01 10:05:00+00', 'paid'),
(2, TIMESTAMPTZ '2026-07-02 08:45:00+00', 'void');

INSERT INTO ticket_lines (ticket_id, line_no, item_name, quantity, charged_unit_price)
VALUES
(1, 1, 'Latte', 2, 4.50),
(1, 2, 'Cookie', 1, 3.00),
(2, 1, 'Cold Brew', 1, 4.75),
(3, 1, 'Latte', 1, 4.50),
(3, 2, 'Cookie', 2, 3.00),
(4, 1, 'Cold Brew', 2, 4.75);

CREATE VIEW paid_ticket_totals AS
SELECT ct.ticket_id,
     cb.branch_name,
     CAST(ct.opened_at AS date) AS sales_day,
     sum(tl.quantity * tl.charged_unit_price) AS ticket_total
FROM cafe_tickets AS ct
JOIN cafe_branches AS cb
ON cb.branch_id = ct.branch_id
JOIN ticket_lines AS tl
ON tl.ticket_id = ct.ticket_id
WHERE ct.status = 'paid'
GROUP BY ct.ticket_id, cb.branch_name, CAST(ct.opened_at AS date);

CREATE VIEW branch_day_sales AS
SELECT branch_name,
     sales_day,
     count(*) AS ticket_count,
     sum(ticket_total) AS gross_sales
FROM paid_ticket_totals
GROUP BY branch_name, sales_day;

CREATE MATERIALIZED VIEW branch_day_sales_cache AS
SELECT branch_name,
     sales_day,
     ticket_count,
     gross_sales
FROM branch_day_sales;

CREATE UNIQUE INDEX branch_day_sales_cache_key
ON branch_day_sales_cache (branch_name, sales_day);

SELECT branch_name, sales_day, ticket_count, gross_sales
FROM branch_day_sales_cache
ORDER BY sales_day, branch_name;

-- break it: uncomment — this aggregate view is not an insert target
-- INSERT INTO branch_day_sales (branch_name, sales_day, ticket_count, gross_sales)
-- VALUES ('center', DATE '2026-07-03', 1, 4.00);

⌘/Ctrl + Enter to run

Views give names to database behavior without leaving SQL. Next, 11.2 moves from named queries to named routines: stored functions and procedures.

Checkpoint

Answer all three to mark this lesson complete