Introduction to Views: Simplify Complex Queries in PostgreSQL

Previously in this course, we covered subqueries in WHERE clauses, which allow you to nest logic to filter your data. Today, we take that concept a step further by learning how to save those complex queries as Views.

What are Views?

In PostgreSQL, a View is a saved SELECT statement that acts as a virtual table. When you create a view, you aren't storing a copy of the data; instead, you are storing the definition of the query. Every time you query the view, PostgreSQL executes the underlying SELECT statement on the fly.

Think of views as "shortcuts" for your most frequent, complex operations. They provide two major benefits:

1. Query simplification: You can hide complex JOIN logic or calculations behind a simple table name.
2. Security and abstraction: You can provide read-only access to specific subsets of data without exposing the underlying base tables.

Creating and Querying a View

To create a view, we use the CREATE VIEW statement. Let’s build a view for our store project that combines customers and orders to create a "Customer Order Summary."

SQL
CREATE VIEW customer_order_summary AS
SELECT 
    c.name AS customer_name,
    o.order_date,
    o.total_amount
FROM customers c
JOIN orders o ON c.id = o.customer_id;

Once the view is created, you can interact with it exactly as if it were a physical table. You don't need to re-write the JOIN or the column aliases ever again:

SQL
-- Querying the view just like a table
SELECT * FROM customer_order_summary 
WHERE total_amount > 100
ORDER BY order_date DESC;

Updating a View

While views are primarily for reading, PostgreSQL allows you to update, insert, or delete rows through a view only if the view is considered "updatable."

A view is updatable if:

• It references exactly one table in its FROM clause.
• It does not contain GROUP BY, DISTINCT, or aggregate functions (SUM, COUNT, etc.).

If your view is a simple projection of a single table, you can modify data through it:

SQL
-- Assuming a view 'recent_products' that just selects from 'products'
UPDATE recent_products 
SET price = 19.99 
WHERE product_id = 5;

If you need to update a complex view (like our customer_order_summary above), you must use a Trigger or perform the update on the base tables directly. Attempting to update a joined view will result in an error, as the database cannot unambiguously determine which base table to modify.

Hands-on Exercise

1. Create a new view called expensive_products that selects all rows from your products table where the price is greater than $50.00.
2. Query the expensive_products view.
3. If you were to add a SUM(price) to this view definition, would it still be updateable? (Hint: Think about why aggregate results don't map back to individual rows).

Common Pitfalls

• Performance Misconceptions: Remember that a view is not a cache. If the base query is slow, the view will be just as slow. For high-load scenarios where you need the performance of a static table, look into database performance: Asynchronous Materialized Views for High-Load Reads.
• Over-nesting: While you can create a view that queries another view, be careful. Deep chains of views make debugging query plans extremely difficult.
• Column Name Conflicts: If you perform a JOIN in your view, ensure all columns have unique names via AS aliases, otherwise, the view creation will fail.

FAQ

Can I delete a view? Yes, use DROP VIEW view_name;. It removes the definition but leaves the underlying tables untouched.

Do views store data? No, they are virtual. If you update a row in the base table, the change is immediately visible through the view.

Why use a view instead of a CTE? Use a CTE when you need a temporary result set for one specific query. Use a View when you want to reuse the logic across multiple sessions or different parts of your application.

Recap

Views provide a powerful layer of abstraction for your database. By turning complex joins into virtual tables, you simplify your application code and keep your schema clean. Always remember that views are "live"—they reflect the state of the underlying data at the moment of the query.

Up next: We will explore how to use ALTER TABLE to modify your existing table structures as your application requirements evolve.