Create and Use Views in PostgreSQL

You’ve been writing the same JOIN across five different reports, and every time the finance team asks for a tweak, you hunt through three screens of SQL to find the right column. That’s the pain this lesson cures: views in PostgreSQL let you save a query as a named, reusable object — so you treat complexity like a table and keep your codebase clean, secure, and maintainable. By the end, you’ll be creating views like a pro and know exactly when they save you (and when they don’t).

The problem this lesson solves

Raw SQL is powerful, but it’s also repetitive. You’ll often find yourself stacking JOINs, WHERE clauses, and aggregations just to answer the same business question — "give me active customers with their latest order total." Paste that query into five different scripts and you’ve got five places to maintain when a column renames or a filter changes.

Worse, giving colleagues or reporting tools direct access to your base tables exposes every column — including sensitive ones like salary or internal_notes. And if you’re building an API, exposing raw table structures leaks implementation details that are painful to change later.

Views solve both problems: they encapsulate complex queries behind a simple name, and they act as a security layer by showing only the columns (and rows) you want. With a view, you query active_customer_totals just like you’d query a table — but the logic lives in one place.

Core concept / mental model

Think of a view as a virtual table — it doesn’t store data itself; it stores the definition of a query. Every time you select from the view, PostgreSQL runs the underlying query against the base tables. The view is a lens, not a copy.

In database terms:

• Base table — the physical storage of rows (e.g., customers, orders).
• View — a named SELECT statement that behaves like a table for reads (and sometimes writes).
• Materialized view — a special kind of view that does store the result (covered briefly in the compare section).

Why “virtual”? Because the data lives in the base tables — the view is just a saved query. If a new order is inserted, a regular view reflects it immediately. That’s both a feature (always current) and a cost (query runs each time).

💡 Pro tip: If your view’s query is heavy and you query it often, you’ll revisit that cost later — that’s exactly when you’ll think about materialized views.

How it works step by step

Creating a view is a three-step mental recipe: write the query → wrap it in CREATE VIEW → query the view by name. Here’s the process:

1. Identify the query you run repeatedly. Start with the SQL you’d normally paste into your reports. For example, a join to show each customer’s total spend.

2. Decide the view’s name and columns. Pick a clear, descriptive name (e.g., customer_totals). Column names come from the SELECT list — you can optionally rename them in the view definition.

3. Run CREATE VIEW. The syntax is simple: sql CREATE VIEW view_name AS SELECT ...;

4. Query the view like a table. SELECT * FROM view_name; — that’s it. You can also use it in JOINs, WHERE, ORDER BY, and even nest views (though keep that in check).

5. Manage permissions. The view’s owner needs privileges on the base tables, but you can GRANT SELECT on the view to other users without granting access to the base tables — that’s the security win.

Behind the scenes: PostgreSQL rewrites your SELECT from the view into the underlying query. It’s not magic — it’s macro-like expansion. That’s why performance depends on the original query’s efficiency.

Hands-on walkthrough

Let’s build a real scenario. Imagine you run an e-commerce store with customers and orders tables.

Set up sample data

CREATE TABLE customers (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT UNIQUE NOT NULL,
  is_active BOOLEAN DEFAULT TRUE
);

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id),
  total NUMERIC(10,2) NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

INSERT INTO customers (name, email) VALUES
  ('Ada Lovelace', 'ada@example.com'),
  ('Alan Turing', 'alan@example.com');

INSERT INTO orders (customer_id, total) VALUES
  (1, 100.00), (1, 250.50), (2, 75.20);

Create your first view

Now create a view that shows each active customer’s total spend:

CREATE VIEW active_customer_totals AS
SELECT
  c.id,
  c.name,
  COALESCE(SUM(o.total), 0) AS total_spent
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE c.is_active = TRUE
GROUP BY c.id, c.name;

Query the view:

SELECT * FROM active_customer_totals ORDER BY total_spent DESC;

Expected output (approximate):

 id |    name    | total_spent
----+------------+-------------
  1 | Ada Lovelace| 350.50
  2 | Alan Turing |  75.20

💡 Pro tip: Use COALESCE to turn NULL (customers with no orders) into 0 — a common edge case in reporting views.

Update data and see the view change

Add a new order — the view instantly reflects it:

INSERT INTO orders (customer_id, total) VALUES (2, 42.00);
SELECT * FROM active_customer_totals;

Now Alan Turing shows 117.20. That’s the virtual nature of regular views — no refresh, always current.

Replace and drop views

If the business logic changes — say you want to include inactive customers too — you can replace the view:

CREATE OR REPLACE VIEW active_customer_totals AS
SELECT
  c.id,
  c.name,
  COALESCE(SUM(o.total), 0) AS total_spent
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name;

Or drop it entirely:

DROP VIEW IF EXISTS active_customer_totals;

⚠️ Watch out: CREATE OR REPLACE can only add columns at the end of the column list — you can’t reorder or remove existing columns with it. For that, drop and recreate.

Compare options / when to choose what

You have three main choices when you need reusable query logic. Here’s a quick comparison:

When to use a view: - You run the same complex query in multiple places. - You want to expose a simplified, safe subset of columns to users or apps. - You need a consistent definition across teams (single source of truth).

When to avoid a view: - The query is extremely slow and you query it constantly — a materialized view might be better. - The view would nest other views dozens of levels deep — debugging becomes a nightmare. - You need to modify data through the view (writeable views exist but have strict rules).

Troubleshooting & edge cases

Reading through "create and use views in postgresql" tutorials, people hit the same snags. Let’s fix them:

1. "Permission denied" on the base table - Symptom: ERROR: permission denied for table customers - Cause: The view owner lacks SELECT on the base tables (or the user running CREATE VIEW doesn’t). - Fix: Grant privileges to the view owner, or create the view as a superuser, then grant SELECT on the view to others.

2. CREATE OR REPLACE fails because column order changed - Symptom: ERROR: cannot drop columns from view - Fix: Drop and recreate the view, or append new columns at the end.

3. View results are stale (you expected fresh data) - Cause: You accidentally created a MATERIALIZED VIEW instead of a regular one. - Fix: Use REFRESH MATERIALIZED VIEW view_name;, or recreate as a regular VIEW.

4. Performance is worse than expected - Symptom: Queries on the view are slower than the raw query. - Cause: PostgreSQL optimizes the underlying query, but if you add filters outside the view, it might not push them down in all cases (especially with aggregates). - Fix: Check the EXPLAIN plan. Sometimes you need to add indexes on the base tables or turn the view into a materialized view or a function.

5. Attempting to insert/update through a view fails - Symptom: ERROR: cannot insert into view ... - Cause: The view isn't automatically updatable (e.g., has GROUP BY or DISTINCT). - Fix: Use INSTEAD OF triggers, or update the base tables directly.

What you learned & what's next

You now know how to create and use views in PostgreSQL: you can encapsulate complex queries, restrict column access, and keep your reporting logic DRY. You can create, replace, and drop views, and you understand the key difference between regular and materialized views — plus the most common pitfalls.

You’ve hit the learning objectives: you can explain the core concept (virtual table) and you completed a hands-on exercise. You’re ready to move to the next lesson in the PostgreSQL Tutorial track — which likely dives into materialized views or indexes to make those heavy queries even faster. Keep practicing: create a view for your own project’s most common query today.