Views

Objectives

By the end of this lesson, you should be able to:

• Save a query as a reusable view with CREATE VIEW
• Query a view exactly like a table
• Explain why a view always reflects current data

💡 Why this matters: A complex query, several joins, filters, and calculations, shouldn’t need to be retyped everywhere it’s needed. A view saves it once, under a name, and every use afterward is as simple as SELECT * FROM that_name.

⚠️ A note on verification: every statement and result in this lesson was run against a real, live PostgreSQL 18 database, using the employees table from earlier modules.

Creating a View

CREATE VIEW high_earners AS
SELECT first_name, last_name, department, salary
FROM employees
WHERE salary > 70000;

CREATE VIEW name AS query saves query under name, a view is not a copy of the data, it’s a stored query, run fresh every time the view is used.

Querying a View

SELECT * FROM high_earners ORDER BY first_name;

 first_name | last_name |  department  |  salary
------------+-----------+---------------+----------
 Alexis     | Moreno    | Marketing    | 71000.00
 Erin       | Castillo  | Engineering  | 74000.00
 Priya      | Desai     | Engineering  | 89000.00
 Taylor     | Nakamura  | Engineering  | 95000.00

A view is queried exactly like a real table, SELECT, WHERE, ORDER BY, even joining it with other tables, all work the same way. Underneath, PostgreSQL substitutes the view’s saved query wherever it’s referenced.

A View Always Reflects Current Data

INSERT INTO employees (first_name, last_name, department, salary, hire_date, email)
VALUES ('Robin', 'Ashworth', 'Engineering', 82000, '2024-01-01', 'robin.ashworth@example.com');

SELECT * FROM high_earners ORDER BY first_name;

 first_name | last_name |  department  |  salary
------------+-----------+---------------+----------
 Alexis     | Moreno    | Marketing    | 71000.00
 Erin       | Castillo  | Engineering  | 74000.00
 Priya      | Desai     | Engineering  | 89000.00
 Robin      | Ashworth  | Engineering  | 82000.00
 Taylor     | Nakamura  | Engineering  | 95000.00

Robin appears in high_earners immediately after being inserted into employees, with no need to recreate the view. Since a view is just a stored query, not stored results, it always reflects whatever is in the underlying table at the moment it’s queried. The next lesson covers a variant that trades this always-fresh behavior for speed.

Dropping a View

DROP VIEW high_earners;

DROP VIEW removes the saved query, this is exactly analogous to DROP TABLE (Module 3), the view (or table) and its definition are gone, though for a view, the underlying data in employees is completely untouched, only the saved shortcut disappears.

Try It

1. Create a view named engineering_team selecting every employee in the Engineering department.
2. Query the view, then insert a new Engineering employee, then query the view again and confirm the new employee appears.
3. Create a view that joins employees with another table from an earlier module, and query it.
4. Explain, in your own words, why DROP VIEW never deletes any actual row of data.

Recap

• CREATE VIEW name AS query saves a query under a name, queried afterward like a table.
• A view stores the query itself, not its results, so it always reflects the underlying table’s current data.
• DROP VIEW removes the saved query without affecting the underlying table’s data.

Next lesson: materialized views, a view that stores its results instead of recalculating them every time.