Skip to content
SQL

SQL Views: CREATE VIEW and Materialized Views in PostgreSQL

Ihsan Ullah · 13 min read
SQL views, Lesson 23 of the SQL course: CREATE VIEW and materialized views in PostgreSQL

SQL course · Module 6: Subqueries and CTEs · Lesson 23 of 40

Quick answer: A SQL view is a saved query with a name. CREATE VIEW name AS SELECT ... stores the query, not the rows, so every time you select from the view, PostgreSQL runs the query again and you always see current data. A materialized view (CREATE MATERIALIZED VIEW) runs the query once and stores the rows, so reading it is fast, but the data stays old until you run REFRESH MATERIALIZED VIEW. Remove them with DROP VIEW and DROP MATERIALIZED VIEW.

In this lesson you will learn:

  • how to create, change and use a SQL view
  • which views you can UPDATE through, and what WITH CHECK OPTION does
  • how materialized views store rows, and how REFRESH (and CONCURRENTLY) work
  • when to use a view, a materialized view, a CTE or a table
  • how to drop views and materialized views

Before you start: Lesson 21, SQL CTE: WITH Clause and CTE vs Subquery and Lesson 17, SQL INSERT, UPDATE and DELETE: Change Data Safely with RETURNING.

We ran every example on PostgreSQL 16.15 with the shop database, in order, and checked the current PostgreSQL 18 manual. Everything here works on newer versions too. This lesson changes the shop data (one city, one new order and, in the exercises, one customer). Run \i shop.sql in psql before you begin, as in Lesson 3, so your ids match ours.

SQL CREATE VIEW: save a query

In Lesson 21 a CTE gave a query a name, but only inside one statement. A view keeps the name in the database, so every later query can use it. Here is a view with one row per order and its total:

CREATE VIEW order_summary AS
SELECT o.id AS order_id, c.name AS customer, o.order_date, o.status,
       SUM(oi.quantity * oi.unit_price) AS total
FROM orders o
JOIN customers c    ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
GROUP BY o.id, c.name, o.order_date, o.status;
CREATE VIEW

Now use it like a table:

SELECT * FROM order_summary
WHERE customer = 'Ayesha Malik'
ORDER BY order_date;
 order_id |   customer   | order_date |  status   | total  
----------+--------------+------------+-----------+--------
        1 | Ayesha Malik | 2026-06-03 | delivered |  47.99
        4 | Ayesha Malik | 2026-06-22 | cancelled |  45.00
       11 | Ayesha Malik | 2026-08-11 | delivered | 181.00
       17 | Ayesha Malik | 2026-09-29 | delivered |  91.00
(4 rows)

Whoever reads from it does not need to know about the three-table join or the GROUP BY. That is the main use of views: hide a long query behind a name, so a rule like “how we calculate an order total” is written once.

A view does not store rows. The manual says: “The view is not physically materialized. Instead, the query is run every time the view is referenced in a query” (PostgreSQL docs: CREATE VIEW). In psql, \dv lists your views:

\dv
            List of relations
 Schema |     Name      | Type |  Owner   
--------+---------------+------+----------
 public | order_summary | view | postgres
(1 row)

Change a view: CREATE OR REPLACE VIEW

CREATE OR REPLACE VIEW swaps in a new query. The new query must give the same columns, but “it may add additional columns to the end of the list”. Adding a lines column at the end works:

CREATE OR REPLACE VIEW order_summary AS
SELECT o.id AS order_id, c.name AS customer, o.order_date, o.status,
       SUM(oi.quantity * oi.unit_price) AS total,
       COUNT(*) AS lines
FROM orders o
JOIN customers c    ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
GROUP BY o.id, c.name, o.order_date, o.status;
CREATE VIEW

Renaming a column in the same way fails. When we changed customer to customer_name:

ERROR:  cannot change name of view column "customer" to "customer_name"
HINT:  Use ALTER VIEW ... RENAME COLUMN ... to change name of view column instead.

Follow the hint, or drop the view and create it again.

Updatable views

Can you change data through a view? For simple views, yes. The manual’s rules, in short: the view must read from exactly one table (or updatable view), with no WITH, DISTINCT, GROUP BY, HAVING, LIMIT, OFFSET or set operation such as UNION at the top level, and no aggregates, window functions or set-returning functions in its column list.

CREATE VIEW pakistan_customers AS
SELECT id, name, city, country
FROM customers
WHERE country = 'Pakistan';

SELECT * FROM pakistan_customers ORDER BY id;
CREATE VIEW
 id |     name      |  city   | country  
----+---------------+---------+----------
  1 | Ayesha Malik  | Lahore  | Pakistan
  4 | bilal ahmed   | Karachi | Pakistan
  7 | Hira Siddiqui | (null)  | Pakistan
 10 | Omar Haddad   | Lahore  | Pakistan
(4 rows)

(As in earlier lessons, \pset null '(null)' shows Hira’s missing city.) This view follows all the rules, so an UPDATE on it changes the real customers table:

UPDATE pakistan_customers SET city = 'Multan' WHERE id = 7;

SELECT id, name, city FROM customers WHERE id = 7;
UPDATE 1
 id |     name      |  city  
----+---------------+--------
  7 | Hira Siddiqui | Multan
(1 row)

order_summary breaks the rules (three tables, GROUP BY, SUM). PostgreSQL refuses:

UPDATE order_summary SET total = 0 WHERE order_id = 1;
ERROR:  cannot update view "order_summary"
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.

That makes sense: no single table row holds the total 47.99. The hint’s triggers are an advanced topic; for now, update the real tables.

WITH CHECK OPTION

An updatable view can let a row escape. In a test transaction, UPDATE pakistan_customers SET country = 'UAE' WHERE id = 7; worked, and the view then showed only 3 rows. We undid it with ROLLBACK (Lesson 19). WITH CHECK OPTION makes PostgreSQL check that changed rows still belong to the view:

CREATE OR REPLACE VIEW pakistan_customers AS
SELECT id, name, city, country
FROM customers
WHERE country = 'Pakistan'
WITH CHECK OPTION;

UPDATE pakistan_customers SET country = 'UAE' WHERE id = 7;
CREATE VIEW
ERROR:  new row violates check option for view "pakistan_customers"
DETAIL:  Failing row contains (7, Hira Siddiqui, hira.siddiqui@example.com, Multan, UAE, 2026-05-25).

The failing row shows all six columns of customers, not only the four in the view, because the change really happens in the table.

SQL materialized views: store the result

A normal view runs its query every time, so a heavy report on big tables can be slow each time. A materialized view runs the query once and keeps the rows: “The query is executed and used to populate the view at the time the command is issued” (PostgreSQL docs: CREATE MATERIALIZED VIEW).

Monthly sales, built on top of our view (::date as in Lesson 8):

CREATE MATERIALIZED VIEW monthly_sales AS
SELECT date_trunc('month', order_date)::date AS month,
       COUNT(*) AS orders,
       SUM(total) AS revenue
FROM order_summary
WHERE status <> 'cancelled'
GROUP BY 1
ORDER BY 1;

SELECT * FROM monthly_sales;
SELECT 5
   month    | orders | revenue 
------------+--------+---------
 2026-06-01 |      3 |  308.98
 2026-07-01 |      5 |  446.14
 2026-08-01 |      3 |  341.73
 2026-09-01 |      4 |  423.99
 2026-10-01 |      5 |  535.37
(5 rows)

SELECT 5 tells you that 5 rows were stored.

The data gets old

Now a new order arrives. Omar Haddad, the customer with no orders, buys the Ergonomic Chair:

INSERT INTO orders (customer_id, order_date, status)
VALUES (10, '2026-11-02', 'pending')
RETURNING id;

INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (23, 9, 1, 259.00);
 id 
----
 23
(1 row)

INSERT 0 1
INSERT 0 1

The normal view sees the new order at once. The materialized view does not:

SELECT order_id, customer, total FROM order_summary WHERE order_id = 23;

SELECT * FROM monthly_sales;
 order_id |  customer   | total  
----------+-------------+--------
       23 | Omar Haddad | 259.00
(1 row)

   month    | orders | revenue 
------------+--------+---------
 2026-06-01 |      3 |  308.98
 2026-07-01 |      5 |  446.14
 2026-08-01 |      3 |  341.73
 2026-09-01 |      4 |  423.99
 2026-10-01 |      5 |  535.37
(5 rows)

No November row. The manual names this trade-off: access to a materialized view “is often much faster”, but “the data is not always current; yet sometimes current data is not needed” (PostgreSQL docs: Materialized Views).

REFRESH MATERIALIZED VIEW

REFRESH runs the stored query again and replaces all the rows:

REFRESH MATERIALIZED VIEW monthly_sales;

SELECT * FROM monthly_sales;
REFRESH MATERIALIZED VIEW
   month    | orders | revenue 
------------+--------+---------
 2026-06-01 |      3 |  308.98
 2026-07-01 |      5 |  446.14
 2026-08-01 |      3 |  341.73
 2026-09-01 |      4 |  423.99
 2026-10-01 |      5 |  535.37
 2026-11-01 |      1 |  259.00
(6 rows)

November is there now. You cannot fix the rows by hand; a materialized view “cannot subsequently be directly updated”:

UPDATE monthly_sales SET orders = 0;
ERROR:  cannot change materialized view "monthly_sales"

REFRESH CONCURRENTLY needs a unique index

A plain refresh “could block other connections which are trying to read from the materialized view” while it runs. CONCURRENTLY lets people keep reading during the refresh (PostgreSQL docs: REFRESH MATERIALIZED VIEW). Try it:

REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales;
ERROR:  cannot refresh materialized view "public.monthly_sales" concurrently
HINT:  Create a unique index with no WHERE clause on one or more columns of the materialized view.

The manual’s rule: this option “is only allowed if there is at least one UNIQUE index on the materialized view which uses only column names and includes all rows”. month is unique here, so index it (Lesson 31 explains indexes; for now, think of it as a UNIQUE rule like in Lesson 16):

CREATE UNIQUE INDEX monthly_sales_month ON monthly_sales (month);

REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales;
CREATE INDEX
REFRESH MATERIALIZED VIEW

The manual adds that without CONCURRENTLY, a refresh that changes many rows “will tend to use fewer resources and complete more quickly”. Use CONCURRENTLY when people must read the view during a refresh.

View vs materialized view: when to use each

CTEviewmaterialized viewtable
stores rowsnonoyesyes
datacurrentcurrentas of the last REFRESHwhat you insert
livesone statementuntil droppeduntil droppeduntil dropped
speed of readingruns the queryruns the queryfast, like a tablefast
you can UPDATE itnosimple views onlyno (REFRESH only)yes
  • Use a CTE to organize one query (Lesson 21).
  • Use a view for a query many people or reports reuse, when the data must be current.
  • Use a materialized view for a slow summary that people read often and that may be a little old, like a nightly dashboard.
  • Use a table for the data itself.

DROP VIEW and DROP MATERIALIZED VIEW

monthly_sales is built on order_summary, so PostgreSQL protects it:

DROP VIEW order_summary;
ERROR:  cannot drop view order_summary because other objects depend on it
DETAIL:  materialized view monthly_sales depends on view order_summary
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

CASCADE would also drop monthly_sales. A materialized view needs its own command:

DROP VIEW monthly_sales;
ERROR:  "monthly_sales" is not a view
HINT:  Use DROP MATERIALIZED VIEW to remove a materialized view.

Drop them in the right order: first the materialized view, then the views. IF EXISTS avoids an error when the object is already gone:

DROP MATERIALIZED VIEW monthly_sales;
DROP VIEW order_summary;
DROP VIEW pakistan_customers;
DROP MATERIALIZED VIEW
DROP VIEW
DROP VIEW
DROP VIEW IF EXISTS order_summary;
NOTICE:  view "order_summary" does not exist, skipping
DROP VIEW

Dropping a view removes only the saved query, never table rows.

Try it yourself

Exercise 1: A product sales view

Create a view product_sales with each product’s name, category and units sold (SUM(quantity)), leaving out cancelled orders. Show the top 3 products by units.

Exercise 2: Watch a materialized view get old

Create a materialized view country_customers with the number of customers per country. Add a customer Zara Ali from Pakistan (signup date 2026-11-05). Check the Pakistan row, refresh, and check again.

Exercise 3: Clean up

Drop the view and the materialized view you created.

Solutions

Solution 1

CREATE VIEW product_sales AS
SELECT p.name, p.category, SUM(oi.quantity) AS units
FROM order_items oi
JOIN orders o   ON o.id = oi.order_id
JOIN products p ON p.id = oi.product_id
WHERE o.status <> 'cancelled'
GROUP BY p.name, p.category;

SELECT * FROM product_sales ORDER BY units DESC, name LIMIT 3;
CREATE VIEW
       name       |  category   | units 
------------------+-------------+-------
 Gel Pens 10-Pack | Stationery  |    16
 Notebook Pack    | Stationery  |    14
 Wireless Mouse   | Electronics |     9
(3 rows)

The ORDER BY and LIMIT belong in the query that reads the view, not in the view.

Solution 2

CREATE MATERIALIZED VIEW country_customers AS
SELECT country, COUNT(*) AS customers
FROM customers
GROUP BY country;

INSERT INTO customers (name, country, signup_date)
VALUES ('Zara Ali', 'Pakistan', '2026-11-05');

SELECT * FROM country_customers WHERE country = 'Pakistan';

REFRESH MATERIALIZED VIEW country_customers;

SELECT * FROM country_customers WHERE country = 'Pakistan';
SELECT 7
INSERT 0 1
 country  | customers 
----------+-----------
 Pakistan |         4
(1 row)

REFRESH MATERIALIZED VIEW
 country  | customers 
----------+-----------
 Pakistan |         5
(1 row)

Zara was counted only after the refresh.

Solution 3

DROP VIEW product_sales;
DROP MATERIALIZED VIEW country_customers;
DROP VIEW
DROP MATERIALIZED VIEW

When you finish, run \i shop.sql in psql again to undo this lesson’s changes (Hira’s city, the new order and Zara). It prints the same lines as in Lesson 3, ending with COMMIT.

Common mistakes

  • Expecting a materialized view to update itself. It shows the data from its last REFRESH, as our November order showed.
  • Updating a view with GROUP BY or a join. cannot update view "order_summary". For a join view, we got Views that do not select from a single table or view are not automatically updatable. Update the base tables.
  • REFRESH CONCURRENTLY without a unique index. cannot refresh materialized view "public.monthly_sales" concurrently. Create a unique index on a column with unique values.
  • DROP VIEW on a materialized view. "monthly_sales" is not a view. Use DROP MATERIALIZED VIEW.
  • Renaming a column with CREATE OR REPLACE VIEW. cannot change name of view column. Use ALTER VIEW ... RENAME COLUMN, or drop and recreate.

Frequently asked questions

Does a view store data?

A normal view does not: it stores only the query, which runs every time you use the view. A materialized view does store the rows, like a table, until the next refresh.

Is a view faster than a normal query?

Not a normal view: PostgreSQL runs the same query either way. A materialized view is faster to read, because the work was done at the last refresh.

How do I refresh a materialized view automatically?

PostgreSQL refreshes it only when you run REFRESH MATERIALIZED VIEW; the manual says it “can be refreshed later upon demand”. To do it on a schedule, run that command from a job scheduler, such as the pg_cron extension.

Summary

  • A SQL view (CREATE VIEW) saves a query, not rows; it always shows current data.
  • Simple one-table views can be updated; WITH CHECK OPTION keeps changed rows inside the view.
  • A materialized view stores the result; it is fast to read but old until REFRESH MATERIALIZED VIEW.
  • REFRESH ... CONCURRENTLY lets people read during the refresh, and it needs a unique index.
  • Drop the materialized view before the view it depends on, and use DROP MATERIALIZED VIEW for it.

Summary tables like monthly_sales are what reports and dashboards read. The data engineering projects list includes pipelines that aggregate key metrics, a good place to try materialized views.

Next lesson

Next: Lesson 24, SQL Window Functions: OVER and PARTITION BY.

Sources

Ihsan Ullah

Written & tested by Ihsan Ullah

Data analyst with 3 years of experience and a Bachelor's degree in Computer Science. I write clear, practical guides on data, SQL, Python and AI.

More about me →

Leave a comment

Your email address will not be published. Required fields are marked *