Creating and Managing Views

Learning Focus

Use this lesson to create, replace, and drop PostgreSQL views; understand what a view is (and is not); and apply safe patterns for schema evolution.

Concept Overview

A view is a named, stored query (usually a SELECT). When you query a view, PostgreSQL expands it and executes the underlying query.

Views do not store data. They store a definition.

Why is it important?

• Reuse: package complex joins and filters into a stable interface
• Consistency: ensure every report uses the same definition
• Safety: expose only certain columns (projection) to some users/roles
• Maintainability: change the underlying tables while preserving a view contract

Where does it fit?

Views are used in:

• reporting and BI layers
• API/query boundaries (read models)
• access control patterns (limit columns/rows)
• refactors where you need a compatibility layer

---

Syntax & Rules

Core Syntax

Create a view:

CREATE VIEW view_name AS
SELECT ...;

Replace a view definition:

CREATE OR REPLACE VIEW view_name AS
SELECT ...;

Drop a view:

DROP VIEW IF EXISTS view_name;

Rename a view:

ALTER VIEW view_name RENAME TO new_name;

Inspect views in psql:

\dv
\d+ view_name

Available Options / Parameters

Feature	Example	Meaning / Notes
Schema-qualified name	CREATE VIEW reporting.v_orders AS ...	avoids search_path surprises
CREATE OR REPLACE	CREATE OR REPLACE VIEW ...	updates definition; may fail if columns/types change incompatibly
Column list	CREATE VIEW v (a, b) AS SELECT ...	rename columns in the view output
WITH CHECK OPTION	... WITH CHECK OPTION	for updatable views; enforces view predicate on writes

Key Rules and Considerations

• A view runs the underlying query each time; it does not cache results.
• Dependencies matter: dropping referenced tables/columns can break a view.
• CREATE OR REPLACE VIEW cannot arbitrarily change an existing view's column types/order in incompatible ways.
• If you need stored results, use a materialized view (separate PostgreSQL feature).

---

Step-by-Step Examples

Use this setup for the examples:

DROP VIEW IF EXISTS v_active_customers;
DROP VIEW IF EXISTS v_customer_revenue;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS customers;

CREATE TABLE customers (
  customer_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email       text NOT NULL UNIQUE,
  created_at  timestamptz NOT NULL DEFAULT now(),
  deleted_at  timestamptz
);

CREATE TABLE orders (
  order_id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint NOT NULL REFERENCES customers(customer_id),
  status      text NOT NULL,
  amount      numeric(12,2) NOT NULL CHECK (amount >= 0),
  created_at  timestamptz NOT NULL DEFAULT now()
);

INSERT INTO customers (email, deleted_at) VALUES
  ('a@example.com', NULL),
  ('b@example.com', NULL),
  ('c@example.com', now());

INSERT INTO orders (customer_id, status, amount, created_at) VALUES
  (1, 'paid',  10.00, TIMESTAMPTZ '2026-03-01 10:00+00'),
  (1, 'paid',  25.00, TIMESTAMPTZ '2026-03-02 10:00+00'),
  (2, 'failed', 5.00, TIMESTAMPTZ '2026-03-02 10:00+00'),
  (2, 'paid',  50.00, TIMESTAMPTZ '2026-03-03 10:00+00');

Example 1: Create a Simple Projection View (Beginner)

CREATE VIEW v_active_customers AS
SELECT customer_id, email, created_at
FROM customers
WHERE deleted_at IS NULL;

SELECT *
FROM v_active_customers
ORDER BY customer_id;

Expected output:

CREATE VIEW
 customer_id |     email       |          created_at
-------------+------------------+-----------------------------
 1           | a@example.com    | ...
 2           | b@example.com    | ...
(2 rows)

Explanation:

• The view hides deleted_at and filters out deleted customers.
• created_at values vary; the important part is that only active rows are returned.

---

Example 2: Create a View That Packages an Aggregation (Intermediate)

CREATE VIEW v_customer_revenue AS
SELECT
  c.customer_id,
  c.email,
  COALESCE(SUM(o.amount) FILTER (WHERE o.status = 'paid'), 0) AS revenue
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE c.deleted_at IS NULL
GROUP BY c.customer_id, c.email;

SELECT *
FROM v_customer_revenue
ORDER BY customer_id;

Expected output:

CREATE VIEW
 customer_id |     email       | revenue
-------------+------------------+---------
 1           | a@example.com    | 35.00
 2           | b@example.com    | 50.00
(2 rows)

Explanation:

• This view becomes a reusable "customer revenue" interface.
• FILTER keeps the aggregation rule readable.

---

Example 3: Replace a View Safely (Intermediate)

Add a new column to the view output without breaking the existing columns:

CREATE OR REPLACE VIEW v_customer_revenue AS
SELECT
  c.customer_id,
  c.email,
  COUNT(o.order_id) AS total_orders,
  COALESCE(SUM(o.amount) FILTER (WHERE o.status = 'paid'), 0) AS revenue
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE c.deleted_at IS NULL
GROUP BY c.customer_id, c.email;

SELECT customer_id, total_orders, revenue
FROM v_customer_revenue
ORDER BY customer_id;

Expected output:

CREATE VIEW
 customer_id | total_orders | revenue
-------------+--------------+---------
 1           | 2            | 35.00
 2           | 2            | 50.00
(2 rows)

Explanation:

• CREATE OR REPLACE updates the view definition.
• If you change types/order incompatibly, PostgreSQL can reject the replace.

---

Example 4: Drop a View (Beginner)

DROP VIEW IF EXISTS v_active_customers;

Expected output:

DROP VIEW

Explanation:

• IF EXISTS makes scripts idempotent.

---

Practical Use Cases

1) Reporting Layer Views

Context: keep BI queries stable.

CREATE VIEW reporting.daily_revenue AS
SELECT date_trunc('day', created_at) AS day, SUM(amount) AS revenue
FROM orders
WHERE status = 'paid'
GROUP BY 1;

2) Column-Level Exposure

Context: share a limited dataset (hide PII columns).

CREATE VIEW public.customer_public AS
SELECT customer_id, created_at
FROM customers;

3) Compatibility During Refactors

Context: keep an old interface while changing underlying tables.

CREATE VIEW v_orders_legacy AS
SELECT order_id, customer_id, amount
FROM orders;

4) Pre-Join Common Dimensions

Context: simplify repeated joins.

CREATE VIEW v_orders_with_customer AS
SELECT o.order_id, o.amount, o.created_at, c.email
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id;

---

Common Mistakes & Troubleshooting

Mistake 1: Treating a View Like Stored Data

What happens: performance surprises because the underlying query runs every time.

Fix: consider materialized views for cached results (with refresh strategy).

---

Mistake 2: Breaking Views During Schema Changes

What happens: dropping/renaming columns breaks dependent views.

Fix: use migrations that update views alongside table changes, and consider a compatibility view during transitions.

---

Mistake 3: Using Views as Security Without Understanding Privileges

What happens: users may still access base tables unless privileges are configured.

Fix: revoke base table privileges and grant privileges on the view intentionally.

Debugging tips:

1. Inspect view definition: \d+ view_name or SELECT pg_get_viewdef('view_name'::regclass, true);.
2. If CREATE OR REPLACE fails, compare old/new column lists and types.
3. Use \dv to list views and confirm schema.
4. When performance is slow, run EXPLAIN on the view query.

---

Best Practices

• ✅ Schema-qualify view names in shared systems; ❌ rely on search_path defaults.
• ✅ Name views with a clear prefix (v_ or domain-based schema); ❌ create ambiguous names.
• ✅ Keep view definitions readable; ❌ hide complex logic without documentation.
• ✅ Use IF EXISTS / OR REPLACE carefully; ❌ change view contracts silently.
• ✅ Manage privileges explicitly; ❌ assume views automatically secure base tables.

---

Hands-On Practice

Use this setup for the exercises:

DROP VIEW IF EXISTS practice_v_paid_orders;
DROP TABLE IF EXISTS practice_orders;

CREATE TABLE practice_orders (
  order_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  status text NOT NULL,
  amount numeric(12,2) NOT NULL CHECK (amount >= 0)
);

INSERT INTO practice_orders (status, amount) VALUES
  ('paid', 10.00),
  ('failed', 5.00),
  ('paid', 25.00);

Exercise 1 (Easy): Create a view

Task: Create practice_v_paid_orders that selects only paid orders.

-- Your SQL here

Solution:

CREATE VIEW practice_v_paid_orders AS
SELECT order_id, amount
FROM practice_orders
WHERE status = 'paid';

---

Exercise 2 (Medium): Query the view

Task: Select from the view and order by order_id.

-- Your SQL here

Solution:

SELECT *
FROM practice_v_paid_orders
ORDER BY order_id;

---

Exercise 3 (Advanced): Replace the view

Task: Replace the view so it also exposes status.

-- Your SQL here

Solution:

CREATE OR REPLACE VIEW practice_v_paid_orders AS
SELECT order_id, status, amount
FROM practice_orders
WHERE status = 'paid';

---

Connection to Other Concepts

Concept	Why it matters
JOINs	views often package joins
Aggregates	many views provide summarized tables
Indexing	views can be fast or slow depending on underlying indexes
Materialized views	alternative when you need cached results
Privileges	views are often part of access control patterns

---

Visual Learning Diagram

flowchart TD
  A[Base Tables] --> B[View Definition\nSELECT ...]
  B --> C[Query View]
  C --> D[PostgreSQL expands view]
  D --> E[Executes underlying query]
  E --> F[Returns rows]

  classDef allNodes fill:#3e3e3e,stroke:#ffffff,stroke-width:2px,color:#f5f5f5
  classDef highlight fill:#3e3e3e,stroke:#ffffff,stroke-width:4px,color:#f5f5f5
  class A,B,C,D,E,F allNodes
  class B highlight

---

Common Pitfalls

Pitfall	Consequence	Prevention
Assuming views cache data	slow repeated queries	use materialized views if needed
Breaking dependencies	runtime errors	migrate tables and views together
Unclear contracts	downstream breakage	version or document view outputs
Privilege confusion	unintended access	grant/revoke explicitly
Overly complex views	hard debugging	build layered views or use CTEs

---

Quick Reference

CREATE VIEW v AS SELECT ...
CREATE OR REPLACE VIEW v AS SELECT ...
ALTER VIEW v RENAME TO v2
DROP VIEW IF EXISTS v
\dv

What's Next

• Next: Updatable vs Read-Only Views - Learn when views can accept INSERT/UPDATE/DELETE.
• Module Overview - Return to Views overview.