Views and Materialized Views

A view is a saved query that you can select from like a table. The examples build views over a small customers-and-orders schema.

-- setup.sql
CREATE TABLE customers (id INT PRIMARY KEY, name TEXT NOT NULL,
                        region TEXT NOT NULL, email TEXT NOT NULL);
CREATE TABLE orders (id INT PRIMARY KEY, customer_id INT REFERENCES customers(id),
                     amount NUMERIC(8, 2) NOT NULL);
INSERT INTO customers VALUES
    (1, 'Ana', 'EU', 'ana@example.com'), (2, 'Ben', 'US', 'ben@example.com'),
    (3, 'Cai', 'EU', 'cai@example.com');
INSERT INTO orders VALUES (10, 1, 120.00), (11, 1, 30.00), (12, 2, 75.00);

$ psql -d sqldemo -f setup.sql
CREATE TABLE
CREATE TABLE
INSERT 0 3
INSERT 0 3

Creating and Querying a View

The view hides a join and an aggregate behind a simple name.

-- create_view.sql
CREATE VIEW customer_totals AS
SELECT c.id, c.name, count(o.id) AS orders, COALESCE(sum(o.amount), 0) AS spent
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name;

SELECT name, orders, spent FROM customer_totals WHERE spent > 50 ORDER BY spent DESC;

$ psql -d sqldemo -f create_view.sql
CREATE VIEW
 name | orders | spent  
------+--------+--------
 Ana  |      2 | 150.00
 Ben  |      1 |  75.00
(2 rows)

Anyone querying customer_totals gets the right join and the right NULL handling without knowing either exists. Cai has no orders, so Cai’s spent is 0 and the WHERE filtered Cai out.

A View Is Always Current

A view stores its query, not its result.

-- view_is_live.sql
INSERT INTO orders VALUES (13, 3, 200.00);
SELECT name, orders, spent FROM customer_totals ORDER BY id;

$ psql -d sqldemo -f view_is_live.sql
INSERT 0 1
 name | orders | spent  
------+--------+--------
 Ana  |      2 | 150.00
 Ben  |      1 |  75.00
 Cai  |      1 | 200.00
(3 rows)

Cai’s new order appears immediately, because selecting from the view re-ran its query. The flip side is cost: an expensive view is expensive every time you read it.

Updatable Views

A view over a single table with no aggregation passes writes through to that table.

-- updatable.sql
CREATE VIEW eu_customers AS
SELECT id, name, region, email FROM customers WHERE region = 'EU';
UPDATE eu_customers SET name = 'Ana M.' WHERE id = 1;
SELECT id, name FROM customers WHERE id = 1;
UPDATE customer_totals SET spent = 0 WHERE id = 1;

$ psql -d sqldemo -f updatable.sql
CREATE VIEW
UPDATE 1
 id |  name  
----+--------
  1 | Ana M.
(1 row)

psql:updatable.sql:6: ERROR:  cannot update view "customer_totals"
DETAIL:  Views containing GROUP BY are not automatically updatable.
HINT:  To enable updating the view, provide an INSTEAD OF UPDATE trigger or an unconditional ON UPDATE DO INSTEAD rule.

The update through eu_customers changed the real customers row. customer_totals is built from GROUP BY, so there is no single row to change: “set Ana’s total spend to 0” has no meaning for the tables underneath, and PostgreSQL says so.

WITH CHECK OPTION

A plain updatable view will accept rows it cannot show.

-- check_option.sql
INSERT INTO eu_customers VALUES (4, 'Dee', 'US', 'dee@example.com');
SELECT count(*) AS dee_visible_in_view FROM eu_customers WHERE id = 4;
CREATE VIEW eu_customers_checked AS
SELECT id, name, region, email FROM customers WHERE region = 'EU'
WITH CHECK OPTION;
INSERT INTO eu_customers_checked VALUES (5, 'Eli', 'US', 'eli@example.com');

$ psql -d sqldemo -f check_option.sql
INSERT 0 1
 dee_visible_in_view 
---------------------
                   0
(1 row)

CREATE VIEW
psql:check_option.sql:7: ERROR:  new row violates check option for view "eu_customers_checked"
DETAIL:  Failing row contains (5, Eli, US, eli@example.com).

Dee was inserted through the EU view as a US customer and immediately disappeared from it: INSERT 0 1, then a count of 0. WITH CHECK OPTION makes the view reject any write that would fall outside its own WHERE, so Eli was refused.

Materialized Views

A materialized view stores its result. Reads are fast, and the data is only as fresh as the last refresh.

-- materialized.sql
CREATE MATERIALIZED VIEW spend_snapshot AS
SELECT customer_id, sum(amount) AS spent FROM orders GROUP BY customer_id;
INSERT INTO orders VALUES (14, 2, 500.00);
SELECT customer_id, spent FROM spend_snapshot WHERE customer_id = 2;
REFRESH MATERIALIZED VIEW spend_snapshot;
SELECT customer_id, spent FROM spend_snapshot WHERE customer_id = 2;

$ psql -d sqldemo -f materialized.sql
SELECT 3
INSERT 0 1
 customer_id | spent 
-------------+-------
           2 | 75.00
(1 row)

REFRESH MATERIALIZED VIEW
 customer_id | spent  
-------------+--------
           2 | 575.00
(1 row)

After the 500.00 order, the snapshot still said 75.00: it had no idea the table changed. REFRESH re-ran the query and stored the new result. Materialized views suit dashboards and reports that tolerate data a few minutes or hours old.

Refreshing Without Blocking Readers

A plain REFRESH locks readers out while it rebuilds. CONCURRENTLY avoids that, but has a requirement.

-- refresh_concurrently.sql
REFRESH MATERIALIZED VIEW CONCURRENTLY spend_snapshot;
CREATE UNIQUE INDEX ON spend_snapshot (customer_id);
REFRESH MATERIALIZED VIEW CONCURRENTLY spend_snapshot;

$ psql -d sqldemo -f refresh_concurrently.sql
psql:refresh_concurrently.sql:2: ERROR:  cannot refresh materialized view "public.spend_snapshot" concurrently
HINT:  Create a unique index with no WHERE clause on one or more columns of the materialized view.
CREATE INDEX
REFRESH MATERIALIZED VIEW

A concurrent refresh compares old and new results row by row, so it needs a unique index to match them up. With the index in place, the refresh succeeded and readers could keep querying the old contents until it finished.

Views as a Privilege Boundary

You can grant access to a view without granting access to the table behind it.

-- view_privileges.sql
CREATE ROLE reporter;
CREATE VIEW customer_directory AS SELECT name, region FROM customers;
GRANT SELECT ON customer_directory TO reporter;
SET ROLE reporter;
SELECT name, region FROM customer_directory ORDER BY name LIMIT 2;
SELECT email FROM customers LIMIT 1;
RESET ROLE;

$ psql -d sqldemo -f view_privileges.sql
CREATE ROLE
CREATE VIEW
GRANT
SET
  name  | region 
--------+--------
 Ana M. | EU
 Ben    | US
(2 rows)

psql:view_privileges.sql:7: ERROR:  permission denied for table customers
RESET

reporter read names and regions through the view but was refused the table, so emails stay private. The view runs with its owner’s rights on customers, which is what makes this work. For row-by-row rules, see row-level security in the security lesson.

Practice

1. Create a view big_orders of orders over 100 and select from it.

-- practice_big_orders.sql
CREATE VIEW big_orders AS SELECT id, customer_id, amount FROM orders WHERE amount > 100;
SELECT * FROM big_orders ORDER BY amount DESC;

$ psql -d sqldemo -f practice_big_orders.sql
CREATE VIEW
 id | customer_id | amount 
----+-------------+--------
 14 |           2 | 500.00
 13 |           3 | 200.00
 10 |           1 | 120.00
(3 rows)

2. Replace customer_totals so it also shows each customer’s region.

-- practice_replace_view.sql
CREATE OR REPLACE VIEW customer_totals AS
SELECT c.id, c.name, count(o.id) AS orders, COALESCE(sum(o.amount), 0) AS spent,
       c.region
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name;
SELECT name, region, spent FROM customer_totals ORDER BY id;

$ psql -d sqldemo -f practice_replace_view.sql
CREATE VIEW
  name  | region | spent  
--------+--------+--------
 Ana M. | EU     | 150.00
 Ben    | US     | 575.00
 Cai    | EU     | 200.00
 Dee    | US     |      0
(4 rows)

3. Show that spend_snapshot is stale again after a new order, then refresh it concurrently.

-- practice_stale.sql
INSERT INTO orders VALUES (15, 3, 10.00);
SELECT spent AS before_refresh FROM spend_snapshot WHERE customer_id = 3;
REFRESH MATERIALIZED VIEW CONCURRENTLY spend_snapshot;
SELECT spent AS after_refresh FROM spend_snapshot WHERE customer_id = 3;

$ psql -d sqldemo -f practice_stale.sql
INSERT 0 1
 before_refresh 
----------------
         200.00
(1 row)

REFRESH MATERIALIZED VIEW
 after_refresh 
---------------
        210.00
(1 row)