Creating and Using Views in PostgreSQL

A view is a named query stored in the database. It acts like a virtual table that you can SELECT from, simplifying complex joins and providing a clean interface for application code.

Creating a View

CREATE VIEW customer_orders AS
SELECT
  c.id AS customer_id,
  c.first_name,
  c.last_name,
  c.email,
  COUNT(o.id) AS order_count,
  COALESCE(SUM(o.total), 0) AS total_spent
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.first_name, c.last_name, c.email;

-- Use it like a regular table
SELECT * FROM customer_orders
WHERE total_spent > 500
ORDER BY total_spent DESC;

The query inside the view runs every time you SELECT from it. The view itself stores no data -- it is simply a saved SQL definition.

Updating and Dropping Views

-- Replace an existing view
CREATE OR REPLACE VIEW customer_orders AS
SELECT
  c.id AS customer_id,
  c.first_name || ' ' || c.last_name AS full_name,
  COUNT(o.id) AS order_count,
  COALESCE(SUM(o.total), 0) AS total_spent
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.first_name, c.last_name;

-- Drop a view
DROP VIEW IF EXISTS customer_orders;

Materialized Views

-- Materialized view stores the result on disk
CREATE MATERIALIZED VIEW monthly_revenue AS
SELECT
  DATE_TRUNC('month', created_at) AS month,
  SUM(total) AS revenue,
  COUNT(*) AS order_count
FROM orders
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month;

-- Query it (fast, reads cached data)
SELECT * FROM monthly_revenue;

-- Refresh when underlying data changes
REFRESH MATERIALIZED VIEW monthly_revenue;

-- Refresh without locking reads
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue;

Unlike regular views, materialized views store computed results on disk. They are much faster to query but must be refreshed manually. Use CONCURRENTLY to refresh without blocking reads (requires a unique index on the view).

When to Use Views

• Regular views -- to simplify complex joins and provide a clean API for application queries
• Materialized views -- for expensive aggregations that do not need real-time data (dashboards, reports)
• To restrict column access by exposing only certain fields to specific roles
• To maintain backward compatibility when refactoring table structures