PostgreSQL – Views

Views in PostgreSQL

In PostgreSQL, a view is a virtual table that is based on the result of a SQL query. Unlike a regular table, a view does not store data physically but provides a dynamic way to represent and query data from one or more tables. Views are useful for simplifying complex queries, enhancing security by restricting access to certain columns or rows, and improving maintainability by encapsulating frequently used queries.

In this tutorial, we’ll explore how to create, use, and manage views in PostgreSQL, along with practical examples to demonstrate their functionality.

---

Why Use Views?

Views provide several advantages:

• Simplification: Complex queries can be saved as a view and reused multiple times, making them easier to manage and read.
• Security: Views can restrict access to sensitive data by exposing only specific columns or rows to users.
• Data Abstraction: Views abstract the underlying table structure, allowing you to modify the table schema without affecting queries that use the view.
• Reusability: Views allow you to reuse the same query logic in different parts of your application.

Basic Syntax for Creating a View

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

This creates a view named view_name that dynamically executes the specified query whenever the view is queried.

---

Example 1: Creating a Simple View

Let’s create a view to show active employees from an employees table. First, set up the employees table and insert some sample data:

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department VARCHAR(100),
    status VARCHAR(10)
);

INSERT INTO employees (employee_name, department, status)
VALUES 
('Alice', 'HR', 'active'),
('Bob', 'IT', 'inactive'),
('Charlie', 'Finance', 'active'),
('David', 'Marketing', 'inactive');

Create a view to display only active employees:

CREATE VIEW active_employees AS
SELECT employee_id, employee_name, department
FROM employees
WHERE status = 'active';

Query the view:

SELECT * FROM active_employees;

Explanation: The active_employees view dynamically retrieves rows where the status is 'active', presenting a simplified and focused dataset.

---

Example 2: Updating Data Through a View

Views in PostgreSQL can often be used to update the underlying table, provided the view meets certain criteria (e.g., it references a single table). Let’s update the status of an employee using the active_employees view:

UPDATE active_employees
SET department = 'Operations'
WHERE employee_name = 'Alice';

Query the employees table to verify the update:

SELECT * FROM employees WHERE employee_name = 'Alice';

Explanation: The update is reflected in the underlying employees table because the view references a single table and does not include aggregations or joins.

---

Example 3: Creating a View with Joins

You can create views that combine data from multiple tables using joins. Let’s create another table named departments and use it in a view:

CREATE TABLE departments (
    department_name VARCHAR(100) PRIMARY KEY,
    manager_name VARCHAR(100)
);

INSERT INTO departments (department_name, manager_name)
VALUES 
('HR', 'John'),
('IT', 'Sarah'),
('Finance', 'Michael'),
('Marketing', 'Anna');

Create a view to display employee details along with their department manager:

CREATE VIEW employee_department AS
SELECT e.employee_name, e.department, d.manager_name
FROM employees e
JOIN departments d ON e.department = d.department_name;

Query the view:

SELECT * FROM employee_department;

Explanation: The employee_department view joins the employees and departments tables, presenting a consolidated view of employee details and their respective department managers.

---

Managing Views

You can perform the following operations to manage views:

• View the definition: Use \d+ view_name in the PostgreSQL command line or pg_catalog.pg_views to see the query used in the view.
• Drop a view: Use DROP VIEW view_name; to remove the view.
• Update a view: Use CREATE OR REPLACE VIEW to modify an existing view.

---

Conclusion

PostgreSQL views are a powerful feature that allow you to simplify complex queries, abstract underlying table structures, and secure sensitive data. By using views effectively, you can enhance the performance and maintainability of your database.