PostgreSQL Views Management

Introduction

In this lab, you will explore PostgreSQL Views Management. The primary goal is to understand and implement different types of views, including simple views and materialized views.

You'll start by defining a simple view based on an employees table, demonstrating how to create a view that selects specific columns. You will then learn how to query and potentially modify data through views. Finally, the lab covers the creation and population of materialized views, along with the manual refreshing of these views to keep them up-to-date.

Define a Simple View

In this step, you will learn how to define a simple view in PostgreSQL. Views are virtual tables based on the result-set of an SQL statement. They are useful for simplifying complex queries, providing abstraction, and controlling data access.

Understanding Views

A view is essentially a stored query. When you query a view, PostgreSQL executes the underlying query and returns the result set as if it were a real table. Views do not store data themselves; they provide a different way to access the data stored in the base tables.

Creating the employees Table

First, let's create a table named employees to work with. Open a terminal and connect to the PostgreSQL database as the postgres user:

sudo -u postgres psql

Now, create the employees table:

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

Next, insert some sample data into the employees table:

INSERT INTO employees (first_name, last_name, department, salary) VALUES
('John', 'Doe', 'Sales', 60000.00),
('Jane', 'Smith', 'Marketing', 75000.00),
('Robert', 'Jones', 'Sales', 62000.00),
('Emily', 'Brown', 'IT', 80000.00),
('Michael', 'Davis', 'Marketing', 70000.00);

You can verify the data by running the following query:

SELECT * FROM employees;

You should see the inserted data in the output.

Defining the employee_info View

Now that we have a table with data, let's create a simple view. This view will show only the first name, last name, and department of each employee. We can define a view named employee_info using the following SQL statement:

CREATE VIEW employee_info AS
SELECT first_name, last_name, department
FROM employees;

This statement creates a view named employee_info that selects the first_name, last_name, and department columns from the employees table.

Querying the View

To query the view, you can use a SELECT statement just like you would with a regular table:

SELECT * FROM employee_info;

This query will return the first name, last name, and department of all employees, as defined in the view.

Describing the View

You can describe the view using the \d command in psql:

\d employee_info

This will show you the view definition and the columns it contains.

Query and Modify Data via Views

In this step, you will learn how to query and modify data through views in PostgreSQL. While views are primarily used for querying data, in some cases, they can also be used to modify the underlying data in the base tables.

Querying Data via Views

As demonstrated in the previous step, querying data through a view is straightforward. You can use a SELECT statement to retrieve data from the view as if it were a regular table.

For example, to retrieve all data from the employee_info view:

SELECT * FROM employee_info;

You can also use WHERE clauses and other SQL constructs to filter and sort the data:

SELECT * FROM employee_info WHERE department = 'Sales';

This query will return only the employees in the Sales department.

Modifying Data via Views

Modifying data through a view is possible under certain conditions. The view must be simple enough for PostgreSQL to determine which base table and columns to update. Generally, a view is modifiable if it meets the following criteria:

• It selects from only one table.
• It does not contain aggregate functions (e.g., SUM, AVG, COUNT).
• It does not contain GROUP BY, HAVING, or DISTINCT clauses.

Let's create another view that includes the employee_id to allow for easier updates:

CREATE VIEW employee_details AS
SELECT employee_id, first_name, last_name, department, salary
FROM employees;

Now, let's try updating the salary of an employee through the employee_details view:

UPDATE employee_details
SET salary = 65000.00
WHERE employee_id = 1;

This statement updates the salary of the employee with employee_id 1 to 65000.00.

You can verify the update by querying the employees table directly:

SELECT * FROM employees WHERE employee_id = 1;

You should see that the salary for employee_id 1 has been updated.

Inserting Data via Views

You can also insert data through a view, provided the view includes all the non-nullable columns of the base table. Since our employee_details view includes all columns of the employees table, we can insert a new employee:

INSERT INTO employee_details (first_name, last_name, department, salary)
VALUES ('David', 'Lee', 'IT', 90000.00);

Note that we don't specify the employee_id because it's a serial column and will be automatically generated.

Verify the insertion:

SELECT * FROM employees WHERE first_name = 'David' AND last_name = 'Lee';

Deleting Data via Views

Similarly, you can delete data through a modifiable view:

DELETE FROM employee_details WHERE first_name = 'David' AND last_name = 'Lee';

Verify the deletion:

SELECT * FROM employees WHERE first_name = 'David' AND last_name = 'Lee';

Important Considerations

• Not all views are modifiable. Complex views with joins, aggregations, or other complex operations are typically read-only.
• Modifying data through views can have performance implications. PostgreSQL needs to translate the view operations into operations on the underlying base tables.
• Be careful when modifying data through views, as changes will directly affect the base tables.

Create and Populate a Materialized View

In this step, you will learn how to create and populate a materialized view in PostgreSQL. Unlike regular views, materialized views store the result set of the query as a physical table. This can significantly improve query performance, especially for complex queries or queries that access data from remote sources. However, the data in a materialized view is not automatically updated when the underlying data changes. You need to refresh it manually or schedule it to be refreshed periodically.

Creating a Materialized View

To create a materialized view, you use the CREATE MATERIALIZED VIEW statement. Let's create a materialized view named employee_salaries that shows the average salary for each department.

CREATE MATERIALIZED VIEW employee_salaries AS
SELECT department, AVG(salary) AS average_salary
FROM employees
GROUP BY department;

This statement creates a materialized view named employee_salaries that calculates the average salary for each department based on the data in the employees table.

Querying the Materialized View

You can query a materialized view just like a regular table:

SELECT * FROM employee_salaries;

This will return the department and the average salary for each department, based on the data that was present in the employees table when the materialized view was created.

Populating the Materialized View

When you create a materialized view, it is automatically populated with the initial data. However, if the underlying data in the employees table changes, the data in the employee_salaries materialized view will not be automatically updated.

Let's insert a new employee into the employees table:

INSERT INTO employees (first_name, last_name, department, salary) VALUES
('Alice', 'Johnson', 'IT', 85000.00);

Now, if you query the employee_salaries materialized view again:

SELECT * FROM employee_salaries;

You will notice that the average salary for the IT department has not changed to reflect the new employee. This is because the materialized view has not been refreshed.

Describing the Materialized View

You can describe the materialized view using the \d command in psql:

\d employee_salaries

This will show you the materialized view definition and the columns it contains.

Refresh a Materialized View Manually

In this step, you will learn how to manually refresh a materialized view in PostgreSQL. As mentioned in the previous step, materialized views do not automatically update when the underlying data changes. To reflect the latest data, you need to refresh them explicitly.

Refreshing the Materialized View

To refresh a materialized view, you use the REFRESH MATERIALIZED VIEW statement. There are two main options:

• REFRESH MATERIALIZED VIEW view_name: This will refresh the materialized view by re-executing the query that defines it. It acquires an ACCESS EXCLUSIVE lock on the materialized view, preventing concurrent access.

• REFRESH MATERIALIZED VIEW CONCURRENTLY view_name: This will refresh the materialized view without blocking concurrent queries. However, it requires that the materialized view has at least one index.

Let's first try refreshing the employee_salaries materialized view using the standard REFRESH MATERIALIZED VIEW command:

REFRESH MATERIALIZED VIEW employee_salaries;

Now, query the employee_salaries materialized view again:

SELECT * FROM employee_salaries;

You should see that the average salary for the IT department has now been updated to reflect the new employee.

Refreshing Concurrently

To refresh the materialized view concurrently, we first need to create a UNIQUE index on it. This is a requirement for concurrent refreshes, as PostgreSQL needs a way to uniquely identify rows to perform the refresh without locking the entire view. The department column in our employee_salaries view is unique because our view groups by department, so we can create a unique index on it.

Let's create a unique index on the department column:

CREATE UNIQUE INDEX idx_employee_salaries_department ON employee_salaries (department);

Now, we can refresh the materialized view concurrently:

REFRESH MATERIALIZED VIEW CONCURRENTLY employee_salaries;

Query the employee_salaries materialized view again to confirm that the data is still up-to-date:

SELECT * FROM employee_salaries;

Choosing the Right Refresh Method

• Use REFRESH MATERIALIZED VIEW for simple materialized views or when you can tolerate a short period of unavailability.
• Use REFRESH MATERIALIZED VIEW CONCURRENTLY for larger materialized views or when you need to minimize disruption to concurrent queries. Remember to create an index on the materialized view first.

Important Considerations

• Refreshing a materialized view can be a resource-intensive operation, especially for large datasets.
• Consider scheduling regular refreshes using a tool like cron to keep the data in the materialized view up-to-date.

Remember to exit the psql shell by typing \q and pressing Enter.

Summary

In this lab, you have learned how to define a simple view in PostgreSQL. You started by creating an employees table with sample data, including columns for employee ID, first name, last name, department, and salary. You then defined a view named employee_info that selects only the first name, last name, and department from the employees table, demonstrating how views can simplify queries and provide a specific perspective on the underlying data.

You also learned how to query and modify data through views, and how to create and refresh materialized views. Materialized views store the result of a query as a table, improving performance for complex queries. You explored different methods for refreshing materialized views, including concurrent refreshing to minimize disruption.