Data Modelling - Views in PostgreSQL

Introduction

Views in PostgreSQL are virtual tables representing the result of a query. They do not store data physically but provide an abstraction layer to simplify complex queries, enhance security, and improve code reusability. This tutorial covers creating, managing, and using views in PostgreSQL.

Creating Views

Views are created using the CREATE VIEW statement. Here is an example:

CREATE VIEW employee_view AS
SELECT first_name, last_name, department
FROM employees
WHERE active = true;
                    

CREATE VIEW
                    

In this example, a view named employee_view is created to display active employees' first names, last names, and departments.

Querying Views

Querying a view is similar to querying a table. Here is an example:

SELECT * FROM employee_view;
                    

 first_name | last_name | department
------------+-----------+------------
 John       | Doe       | Sales
 Jane       | Smith     | Marketing
(2 rows)
                    

This query selects all rows from the employee_view.

Updating Views

While views are read-only by default, you can update the underlying tables through views by using INSTEAD OF triggers. Here is an example:

CREATE OR REPLACE VIEW employee_view AS
SELECT first_name, last_name, department
FROM employees
WHERE active = true;
                    

CREATE VIEW
                    

This statement updates the definition of employee_view if it already exists.

Dropping Views

Views can be dropped using the DROP VIEW statement. Here is an example:

DROP VIEW employee_view;
                    

DROP VIEW
                    

This statement drops the employee_view.

Materialized Views

Materialized views store the query result physically, allowing for faster query performance at the cost of storage space. Here is an example:

CREATE MATERIALIZED VIEW employee_mv AS
SELECT first_name, last_name, department
FROM employees
WHERE active = true;
                    

CREATE MATERIALIZED VIEW
                    

This example creates a materialized view named employee_mv.

Refreshing Materialized Views

Materialized views need to be refreshed to reflect changes in the underlying tables. Here is an example:

REFRESH MATERIALIZED VIEW employee_mv;
                    

REFRESH MATERIALIZED VIEW
                    

This statement refreshes the employee_mv to reflect the current state of the employees table.

Best Practices

Here are some best practices for using views in PostgreSQL:

• Use views to simplify complex queries and improve code reusability.
• Use materialized views for performance-critical queries that do not need real-time data.
• Regularly refresh materialized views to keep data up to date.
• Use INSTEAD OF triggers for updating views when necessary.