Views in PostgreSQL: A Complete Guide with Examples

When working with relational databases like PostgreSQL, you often need to simplify complex queries, enforce data security, or speed up reporting. That’s where Views come in.

A view is essentially a virtual table defined by a query. Instead of writing the same complex query again and again, you save it as a view and query it like a table.

In this blog, we’ll explore the different types of views in PostgreSQL, their use cases, pros and cons, examples, and best practices.

---

🏷️ Types of Views in PostgreSQL

---

1. Simple View

📌 What is it?

A view built from a single table without joins or aggregations.

✅ Why use it?

• Hide unnecessary columns
• Simplify queries
• Enforce limited access

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name TEXT,
    department TEXT,
    salary NUMERIC,
    active BOOLEAN
);

-- Simple view showing only basic employee info
CREATE VIEW employee_public_info AS
SELECT id, name, department
FROM employees;

SELECT * FROM employee_public_info;

⚖️ Pros: Lightweight, updatable, always real-time

⚖️ Cons: Doesn’t reduce computation cost

---

2. Complex View

📌 What is it?

A view involving joins, group by, or aggregates.

✅ Why use it?

• Simplify reporting queries

• Centralize business logic
  

CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    employee_id INT,
    amount NUMERIC,
    sale_date DATE
);

-- Complex view: total sales per department
CREATE VIEW department_sales AS
SELECT e.department, SUM(s.amount) AS total_sales
FROM employees e
JOIN sales s ON e.id = s.employee_id
GROUP BY e.department;

SELECT * FROM department_sales;

⚖️ Pros: Great for dashboards, reduces repeated SQL code

⚖️ Cons: Usually not updatable, may be slow on large datasets

---

3. Materialized View

📌 What is it?

A pre-computed, stored result set of a query. Unlike normal views, it stores data on disk.

✅ Why use it?

• Speed up expensive queries

• Power analytics dashboards
  

CREATE MATERIALIZED VIEW monthly_sales AS
SELECT DATE_TRUNC('month', sale_date) AS month,
       SUM(amount) AS total
FROM sales
GROUP BY DATE_TRUNC('month', sale_date);

-- Refresh when new data arrives
REFRESH MATERIALIZED VIEW monthly_sales;

SELECT * FROM monthly_sales ORDER BY month;

⚖️ Pros: Huge performance boost, can be indexed

⚖️ Cons: Data can get stale, needs manual refresh

---

4. Updatable View

📌 What is it?

A view where you can INSERT, UPDATE, DELETE rows directly.

✅ Why use it?

• Provide restricted updates without exposing full table

-- Only active employees
CREATE VIEW active_employees AS
SELECT *
FROM employees
WHERE active = true;

-- Update via the view
UPDATE active_employees SET active = false WHERE id = 3;

⚖️ Pros: Secure way to allow controlled updates

⚖️ Cons: Works only with simple queries

---

5. Read-Only View

📌 What is it?

A view that cannot be updated (complex logic, joins, or aggregates).

✅ Why use it?

• Safely expose analytics reports

-- Employees with high sales
CREATE VIEW top_performers AS
SELECT employee_id, SUM(amount) AS total_sales
FROM sales
GROUP BY employee_id
HAVING SUM(amount) > 50000;

SELECT * FROM top_performers;

⚖️ Pros: Safe for reporting

⚖️ Cons: Can’t modify data through it

---

6. Recursive View

📌 What is it?

A view built using a recursive CTE to handle hierarchies.

✅ Why use it?

• Org charts, folder structures, category trees

-- Hierarchy of employees and managers
CREATE VIEW employee_hierarchy AS
WITH RECURSIVE emp_cte AS (
    SELECT id, name, manager_id
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    JOIN emp_cte c ON e.manager_id = c.id
)
SELECT * FROM emp_cte;

SELECT * FROM employee_hierarchy;

⚖️ Pros: Perfect for recursive data

⚖️ Cons: Can get slow for deep hierarchies

---

🛒 Real-World Example: Views in an E-commerce App

Imagine you’re building an e-commerce platform. Here’s how different views fit in:

• Simple View → customer_directory (hide sensitive columns)

• Complex View → category_revenue (total revenue per category)

• Materialized View → daily_sales (fast dashboards with precomputed data)

• Updatable View → active_products (only allow editing active products)

• Read-Only View → top_customers (find VIPs safely)

• Recursive View → category_hierarchy (nested categories navigation)

By mixing these types, your database becomes secure, performant, and easy to query.

---

⚡ Performance Best Practices

1. Index Materialized Views
   

   CREATE UNIQUE INDEX idx_daily_sales_date ON daily_sales(order_date);
   
   

2. Refresh Strategy

-   Refresh materialized views with `REFRESH MATERIALIZED VIEW` at scheduled times (e.g., nightly with `pg_cron`).

1. Optimize Complex Views

-   Break into smaller views or switch to materialized views if performance is bad.

1. Use WITH CHECK OPTION for updatable views
   

   CREATE VIEW active_products AS
   SELECT * FROM products WHERE is_active = true
   WITH CHECK OPTION;
   
   

2. Recursive Views: Add Depth Limit
   

   WHERE p.depth < 10;
   
   

---

🏆 Key Takeaways

• Use simple & complex views for query simplification and security.

• Use materialized views for performance-heavy reporting.

• Use updatable views with care.

• Use recursive views for hierarchical data.

• Always balance freshness vs performance.

With the right type of view, you can make your PostgreSQL database faster, safer, and easier to work with 🚀