Views

A view is a saved, named SELECT query that behaves like a virtual table. It doesn't store any data of its own — every time you query the view, PostgreSQL runs the underlying query fresh and hands you the result, as if you'd typed the whole query yourself.

Creating a view

Create a view

SQL

CREATE VIEW active_customers AS
SELECT id, name, email, signup_date
FROM customers
WHERE status = 'active';

Once created, active_customers can be queried exactly like a regular table:

SQL

SELECT name, email
FROM active_customers
ORDER BY signup_date DESC
LIMIT 10;

name       | email
-----------+-----------------------
Ada King   | ada@example.com
Sam Lee    | sam@example.com

Why use a view

Simplify repeated queries. If several parts of an application (or several analysts) all run the same complex join with several conditions, wrapping it in a view means everyone writes SELECT * FROM view_name instead of repeating the full query.
Restrict what a user or role can see. A view can expose only certain columns or rows of an underlying table — for example, hiding a salary column or filtering out soft-deleted rows — and you grant access to the view rather than the base table. This is a common, lightweight way to enforce a security boundary at the database layer.
Give a stable name to a query shape. Application code can depend on a view's column names staying the same even if the underlying tables are later restructured, as long as the view definition is updated to match.

Replacing and dropping a view

Use CREATE OR REPLACE VIEW to redefine an existing view without dropping it first — useful because dropping a view would also revoke any permissions granted on it and break anything that depends on it existing.

SQL

CREATE OR REPLACE VIEW active_customers AS
SELECT id, name, email, signup_date, last_login
FROM customers
WHERE status = 'active';

A view is removed with DROP VIEW:

SQL

DROP VIEW active_customers;

Note

CREATE OR REPLACE VIEW can add new columns to the end of the column list, but it cannot remove or reorder existing columns, or change a column's type. To do that, you must DROP VIEW and CREATE VIEW again from scratch.

A view always sees current data

Because a view has no storage of its own — it is just a saved query definition — it is always up to date. Every SELECT against a view re-executes the underlying query against the current state of the base tables, so a view can never show stale data.

Tip

That freshness comes at a cost: if the underlying query is expensive (large joins, heavy aggregation), every single read through the view pays that cost again. When you need the speed of a precomputed result instead, PostgreSQL offers materialized views, which physically store the query result and must be refreshed on demand rather than recomputed on every read.