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 🚀