Views in PostgreSQL with Examples

Views in PostgreSQL

The views in PostgreSQL are virtual tables that are defined by a query. They allow you to encapsulate complex queries into reusable and simplified structures, which can be queried just like regular tables. Views provide an abstracted layer over the underlying data, allowing you to present a specific subset of the data or perform additional calculations without altering the original data.

Views in PostgreSQL have various use cases that can help simplify data querying, enhance security, and improve code maintainability.

• Data Simplification
• Security and Access control

Managing Views

Let's go through an example of how to manage views in PostgreSQL.

Imagine you have a database with a table called students that contains information about students, such as their names, ages, and grades. You want to create a view that shows only the names and ages of students who have passed (grades greater than or equal to 60).

Here's how you can do it step by step:

Step 1: Create the Table

First, let's create the students table:

Step 2: Insert Data

Now, insert some sample data into the students table:

Step 3: Create the View

Create a view named passed_students that shows the names and ages of students who have passed:

Step 4: Query the View

Now you can query the passed_students view as if it were a regular table:

This query will return the names and ages of students who have passed:

Step 5: Update the Data

Let's update the grade of a student in the students table and see how it affects the view:

Step 6: Query the View Again

If you now query the passed_students view again:

The result will automatically reflect the changes made to the underlying students table:

Remember that the view is based on the query, so any updates, inserts, or deletes in the underlying table that match the view's criteria will be reflected in the view.

Creating Views

To create a view, you use the CREATE VIEW statement followed by the view's name and the SQL query that defines the view. After a view is created, you can query it just like a regular table using the SELECT statement. You can also perform other operations on a view, such as filtering, sorting, and joining, just as you would with a table.

Syntax:

Creating Updatable views in PostgreSQL

Creating updatable views in PostgreSQL involves defining a view that allows you to perform data modification operations such as INSERT, UPDATE, and DELETE. However, not all views can be made updatable. Certain conditions must be met for a view to be updatable:

• The view must have only one base table in its FROM clause.
• The view must not have any of the following:
  • Aggregate functions (SUM, COUNT, etc.)
  • Set operations (UNION, INTERSECT, EXCEPT)
  • DISTINCT
  • GROUP BY
  • HAVING
• The view must have all the columns of the base table that are needed for data modification.
• The view must not use any subqueries in the SELECT statement.

Example

Let's create an updatable view step by step:

Assuming you have a table called employees:

Output

Let's create an updatable view that displays employees with a salary above a certain threshold:

Output

In this case, the high_salary_employees view includes employees with salaries above $55,000.

Now, you can perform data modification operations on the high_salary_employees view:

Insertion:

Update

Deletion

All these operations will be reflected in the underlying employees table as long as they meet the conditions of the view's definition.

Output

And the employees table would look like this:

Creating Recursive views in PostgreSQL

A recursive view in PostgreSQL is a type of view that allows you to perform recursive queries on hierarchical or recursive data structures, such as hierarchical tree structures or graphs. Recursive views are defined using a combination of common table expressions (CTEs) and the UNION or UNION ALL operators. They are useful for querying and processing data with recursive relationships.

Example

Suppose we have an employees table with the following data:

Create recursive view in PostgreSQL to query hierarchical data, such as an organizational chart:

Output

The recursive query starts with the CEO (employee_id 1) and then identifies their direct reports (Manager A and Manager B). It continues to descend the hierarchy, listing employees under their respective managers.

Changing Views in PostgreSQL

You can change the definition of a view in PostgreSQL using the CREATE OR REPLACE VIEW statement. This allows you to modify the query or columns of an existing view without needing to drop and recreate it. Here's the syntax:

Replace view_name with the name of your existing view, and adjust the SELECT statement to modify the columns, tables, or conditions as needed. Keep in mind that the new query should have the same number of columns and compatible data types as the original view.

Example

Let's say you have an existing view named sales_view that displays sales information from a sales table:

Create sales_view query

Output

In this case, the view definition is being changed to show sales data from January 1, 2023, onward.

Remember that using CREATE OR REPLACE VIEW will alter the view's definition, but it won't change the underlying data or affect the data in any way.

Altering Views in PostgreSQL

Altering a view in PostgreSQL means modifying the definition of an existing view. You can change the underlying query of the view, add or remove columns, or adjust its properties. PostgreSQL does not directly support the ALTER VIEW command for modifying the view's query or columns.

However, there are some limitations on what can be altered in a view due to the complexity of the underlying queries and the potential impact on data consistency.

Altering the View's query

You can change the query that defines the view to update the data it presents or to change the way it organizes and filters the data.

What are Materialized Views

PostgreSQL also supports materialized views. Unlike regular views, materialized views store the result of the query as physical data. This can improve query performance, especially for complex calculations and aggregations. However, materialized views need to be manually refreshed to stay up-to-date with the underlying data.

Example

Let's say you have a database with a table called sales containing information about sales transactions, and you want to create a materialized view that aggregates the total sales amount for each product category.

First, let's assume you have a table like this:

Assuming sample data in the sales and products tables:

sales Table:

products Table:

Now, you can create a materialized view that calculates the total sales amount for each product category:

Output

In this example, total_sales_by_category is the name of the materialized view. It calculates the total sales amount for each product category by joining the sales table with the products table on the product_id column and then using the SUM function to aggregate the sales amounts. The result is stored in the materialized view.

You can refresh the materialized view to update its data based on changes in the underlying tables using the REFRESH command:

Keep in mind that materialized views store the result data physically, and you need to refresh them manually or use scheduled jobs to ensure the data remains up to date with changes in the underlying tables. This can be especially useful for scenarios where complex calculations or aggregations are involved and you want to precompute the results for better performance.