Views in PostgreSQL

A view is a saved query that behaves like a virtual, read-through table — it doesn't store data itself, it re-runs its underlying query every time you select from it.

Creating a View

CREATE VIEW high_earners AS
SELECT id, name, department, salary
FROM employees
WHERE salary > 60000;

Using It Like a Table

SELECT * FROM high_earners WHERE department = 'Engineering';

Since the view is a saved query, this always reflects the current data in employees — there's no risk of it going stale.

Why Use a View?

• Simplify repeated complex queries — hide a big join or aggregation behind a simple name.
• Restrict access — grant a user permission on the view (e.g. certain columns only) instead of the full underlying table.
• Stable interface — application code can query a view's consistent shape even if the underlying table structure changes later.

Replacing a View

CREATE OR REPLACE VIEW high_earners AS
SELECT id, name, department, salary
FROM employees
WHERE salary > 70000;

CREATE OR REPLACE only works if you keep the same column names/types in the same order — otherwise you must DROP and recreate it.

Updatable Views

Simple views (based on a single table, with no aggregation) can often be updated directly — the change passes through to the underlying table.

UPDATE high_earners SET salary = 75000 WHERE id = 1;
-- actually updates the row in employees

Dropping a View

DROP VIEW high_earners;

Common Errors & Troubleshooting

• ERROR: cannot change name of view column — CREATE OR REPLACE VIEW requires the same column names/order as before; if you need to change the shape, DROP VIEW and recreate it instead.
• ERROR: cannot drop view ... because other objects depend on it — another view or function references this one; drop those first, or use DROP VIEW ... CASCADE if removing them too is intended.
• Trying to UPDATE/INSERT through a complex view and getting an error — only simple, single-table views are automatically updatable; a view involving a join, GROUP BY, or DISTINCT isn't, unless you add an INSTEAD OF trigger (an advanced technique beyond this lesson).

Key Takeaways

• A view stores a query, not data — it always reflects the live state of its underlying table(s).
• Views are a great way to hide complexity and control access without duplicating data.
• Only simple, single-table views are directly updatable; complex views (joins, aggregates) generally aren't.