PostgreSQL Views

A View in PostgreSQL is a virtual table based on the result of a SQL query. Views provide a way to simplify complex queries, enhance security, and encapsulate frequently used logic. They do not store data themselves but dynamically fetch data from the underlying tables.

1. Basic Syntax

-- Create a simple view
            CREATE VIEW view_name AS
            SELECT column1, column2
            FROM table_name
            WHERE condition;

2. Example: Simple View

-- View of employees in IT department
            CREATE VIEW it_employees AS
            SELECT id, name, department, salary
            FROM employees
            WHERE department = 'IT';

            -- Querying the view
            SELECT * FROM it_employees;

3. Views with Joins

Views can include joins to combine data from multiple tables.

-- View of employees with department names
            CREATE VIEW employee_details AS
            SELECT e.id, e.name, d.department_name, e.salary
            FROM employees e
            JOIN departments d ON e.department_id = d.id;

            -- Querying the view
            SELECT * FROM employee_details;

4. Views with Aggregates

Views can include aggregate functions to summarize data.

-- View of total salary per department
            CREATE VIEW department_salary AS
            SELECT department_id, SUM(salary) AS total_salary, AVG(salary) AS avg_salary
            FROM employees
            GROUP BY department_id;

            -- Querying the view
            SELECT * FROM department_salary;

5. Updating Views

Simple views can be updatable if they reference only a single table without aggregates, joins, or complex expressions.

-- Updating a simple view
            UPDATE it_employees
            SET salary = salary * 1.05
            WHERE id = 101;

⚠️ Complex views with joins or aggregates are usually read-only.

6. Dropping a View

-- Drop a view
            DROP VIEW IF EXISTS it_employees;

7. Benefits of Views

• Simplify complex queries by encapsulating logic.
• Enhance security by restricting access to specific columns or rows.
• Improve maintainability of SQL code.
• Provide a consistent interface to underlying tables.

8. Best Practices

• Name views clearly to indicate their purpose.
• Use views for frequently used queries to avoid repeating logic.
• Be aware of performance implications for large or complex views.
• Consider materialized views if query performance is critical and the data doesn’t need to be real-time.

Conclusion

PostgreSQL Views are a powerful tool for simplifying queries, improving security, and maintaining cleaner database structures. By using views effectively, you can provide reusable, readable, and secure ways to access your data. In the next tutorial, we will explore SQL Functions, which allow encapsulating reusable logic and operations inside the database.