PostgreSQL - Views

PostgreSQL - Views

In PostgreSQL, a view is a stored SELECT query that acts as a virtual table. It doesn’t store data itself but derives its data from one or more underlying base tables. Views are often used to simplify complex queries, enforce security, and present a consistent data interface.

Key Concepts

• Data Abstraction: Views hide the complexity of underlying table structures and joins, presenting data in a simple, logical format.
• Security: You can grant users access to a view without granting them access to the underlying tables, effectively restricting them to specific columns or rows.
• Simplicity: A view can encapsulate a complex, multi-table JOIN query, allowing users to query it as if it were a single table.

Creating Views

Views are created using the CREATE VIEW statement. The OR REPLACE clause is commonly used to update an existing view definition without having to drop it first.

Syntax

CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE [condition];

The TEMP or TEMPORARY keyword creates a view that is automatically dropped at the end of the current session.

Example: Creating a Simple View

Let’s assume we have an employees table:

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100) UNIQUE,
    salary NUMERIC(10, 2),
    department_id INT
);

INSERT INTO employees (first_name, last_name, email, salary, department_id)
VALUES
('Alice', 'Williams', 'alice.w@example.com', 75000.00, 1),
('Bob', 'Johnson', 'bob.j@example.com', 82000.00, 1),
('Charlie', 'Brown', 'charlie.b@example.com', 68000.00, 2);

Now, let’s create a view that only shows employee names and emails, hiding sensitive salary information.

CREATE OR REPLACE VIEW employee_directory AS
SELECT first_name, last_name, email
FROM employees
ORDER BY last_name, first_name;

You can now query this view just like a table:

SELECT * FROM employee_directory;

This would produce the following result:

 first_name | last_name |         email
------------+-----------+-----------------------
 Charlie    | Brown     | charlie.b@example.com
 Bob        | Johnson   | bob.j@example.com
 Alice      | Williams  | alice.w@example.com
(3 rows)

Updatable Views and INSTEAD OF Triggers

A view is automatically updatable (i.e., you can run INSERT, UPDATE, or DELETE on it) if it meets certain conditions, primarily that it’s based on a single table and doesn’t use aggregation, DISTINCT, GROUP BY, etc. For complex, non-updatable views, you can define an INSTEAD OF trigger to specify what should happen on an attempted modification.

Note: In the past, RULEs were sometimes used for this purpose, but INSTEAD OF triggers are the modern and more flexible best practice.

Materialized Views

For performance-critical queries, PostgreSQL offers MATERIALIZED VIEWs. Unlike a standard view, a materialized view physically stores its result set. This provides faster read access but requires you to manually REFRESH the view to see changes from the underlying tables.

CREATE MATERIALIZED VIEW high_earners AS
SELECT id, first_name, last_name FROM employees WHERE salary > 80000;

-- To update the data in the materialized view:
REFRESH MATERIALIZED VIEW high_earners;

Dropping Views

To remove a view, use the DROP VIEW statement. Use CASCADE to automatically drop any objects that depend on the view.

DROP VIEW view_name; -- Fails if other objects depend on it (RESTRICT is default)
DROP VIEW view_name CASCADE; -- Drops the view and dependent objects

To drop the employee_directory view we created:

DROP VIEW employee_directory;