Views

A view is a named query stored in the database. You query it like a table, but PostgreSQL runs the underlying SQL each time. Views are one of the most practical tools for managing complexity, controlling access, and keeping application code clean. Rather than duplicating a complex join in every query that needs it, you define it once as a view and reference it by name.

Creating and Querying Views

Creating a view is as simple as wrapping a SELECT in CREATE VIEW. Once created, the view behaves exactly like a table from the caller’s perspective — the complexity is hidden behind the name.

-- Define the view once
CREATE VIEW active_customers AS
SELECT id, name, email, created_at
FROM customers
WHERE deleted_at IS NULL;

-- Query it exactly like a table
SELECT * FROM active_customers WHERE name ILIKE 'smith%';

PostgreSQL merges the view definition with your query at planning time, so the final execution is as efficient as writing the full query yourself.

Drop a view with:

DROP VIEW active_customers;

To update a view’s definition without dropping it first, use CREATE OR REPLACE VIEW. The replacement must include all the original columns in the same order — you can add new columns at the end but cannot remove or reorder existing ones.

CREATE OR REPLACE VIEW active_customers AS
SELECT id, name, email, phone, created_at  -- phone column added
FROM customers
WHERE deleted_at IS NULL;

When Views Help

Views solve three distinct problems, each of which comes up constantly in real applications.

Simplification — hide a complex multi-table join behind a clean name that the rest of the codebase can use without repeating the logic:

-- A complex join defined once and reused everywhere
CREATE VIEW order_summary AS
SELECT
  o.id        AS order_id,
  c.name      AS customer_name,
  o.created_at,
  o.status,
  SUM(oi.qty * 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.created_at, o.status;

Security — expose only specific columns or rows to a role. Grant SELECT on the view but not the underlying table. The role sees exactly what you want and nothing else:

CREATE VIEW public_products AS
SELECT id, name, description, price
FROM products
WHERE is_published = TRUE;
-- Sensitive columns like cost, supplier_id, and unpublished rows are invisible

GRANT SELECT ON public_products TO web_reader;

API stability — applications query the view. If the underlying schema changes (a column renamed, a table split), update the view definition rather than every query in every application:

-- If products is split into products + product_details,
-- update this view and no application query needs to change
CREATE OR REPLACE VIEW public_products AS
SELECT p.id, p.name, pd.description, p.price
FROM products p
JOIN product_details pd ON pd.product_id = p.id
WHERE p.is_published = TRUE;

Updatable Views

A view is automatically updatable in PostgreSQL when it meets these conditions:

• Based on a single table or updatable view
• No DISTINCT, GROUP BY, HAVING, UNION, LIMIT, or aggregate functions
• No window functions

CREATE VIEW active_users AS
SELECT id, username, email
FROM users
WHERE is_active = TRUE;

-- These all work on the simple view — writes pass through to the underlying table
UPDATE active_users SET email = 'new@example.com' WHERE id = 5;
INSERT INTO active_users (username, email) VALUES ('alice', 'alice@example.com');
DELETE FROM active_users WHERE id = 9;

For more complex views, you can define INSTEAD OF triggers to handle writes manually.

WITH CHECK OPTION

WITH CHECK OPTION prevents inserts or updates through a view that would make the modified row invisible to that view. Without it, you could insert a row through active_users with is_active = FALSE, the insert would succeed, and the row would then be invisible through the view — a confusing outcome.

CREATE VIEW active_users AS
SELECT id, username, is_active
FROM users
WHERE is_active = TRUE
WITH CHECK OPTION;

-- This fails: the inserted row has is_active = FALSE,
-- which would not appear in the view — CHECK OPTION blocks it
INSERT INTO active_users (username, is_active) VALUES ('bob', FALSE);
-- ERROR: new row violates check option for view "active_users"

Materialized Views

A materialized view runs its query once and stores the result on disk. Subsequent queries read the cached result rather than re-running the underlying query against the live tables. This is the right tool for expensive aggregations — monthly sales reports, analytics dashboards, pre-computed leaderboards — where recalculating on every read would be prohibitively slow.

CREATE MATERIALIZED VIEW monthly_sales AS
SELECT
  date_trunc('month', created_at) AS month,
  SUM(total)                       AS revenue,
  COUNT(*)                         AS order_count
FROM orders
WHERE status = 'completed'
GROUP BY 1
ORDER BY 1;

Query it like any view or table:

SELECT * FROM monthly_sales WHERE month >= '2024-01-01';

Refresh the data manually when you want it updated:

-- Locks the view during refresh — readers are blocked until it completes
REFRESH MATERIALIZED VIEW monthly_sales;

-- Refreshes without blocking concurrent reads (requires a UNIQUE index on the view)
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales;

For CONCURRENTLY to work, add a unique index on the materialized view:

CREATE UNIQUE INDEX ON monthly_sales(month);

You can automate refreshes with pg_cron or a scheduled job in your application.

When to Use Materialized Views

Use a materialized view when:

• The query is expensive (many joins, large aggregations) and the data doesn’t change every second
• You can tolerate slightly stale data (refresh every 5 minutes, hourly, or nightly)
• You want to index the result set for even faster lookups

Use a regular view when:

• Data must always be current
• The underlying query is fast enough
• You mainly want to simplify or secure access

Row-Level Security with Views

Views are a simple alternative to row-level security policies for basic access isolation. A view can filter rows based on the current database user, ensuring each user only sees their own records regardless of which query they run.

CREATE VIEW my_orders AS
SELECT * FROM orders
WHERE customer_id = (
  SELECT id FROM customers WHERE email = current_user
  -- current_user is the database login name of the connected role
);

Each user querying my_orders sees only their own rows. This is straightforward but requires one view per access pattern. PostgreSQL’s ENABLE ROW LEVEL SECURITY with policies is more flexible for complex cases.