In Postgres, a view is not a real table(not physically materialized) but a pseudo-table. However, it can be accessed as an ordinary/real table using a SELECT statement. In PostgreSQL, a VIEW can be defined/created based on single or multiple tables or from other views. For this purpose, the CREATE VIEW statement is used in Postgres.
This post demonstrates how to create a view in Postgres using suitable examples.
How to Create a View in Postgres?
Use the below-provided syntax to create a new view in Postgres:
CREATE[OR REPLACE] VIEW viewName AS SELECT col_list FROM tab_name [WHERE condition];
Let’s comprehend the above syntax line-by-line:
- OR REPLACE is an optional clause/parameter that replaces the already existing view.
- Skipping the OR REPLACE parameter may cause an error if the view already exists.
- Specify the column names in place of the “col_list” parameter to add the columns of your choice to the view.
- tab_name represents a table based on which the view will be created.
- WHERE is an optional clause that specifies a particular condition(s).
Let’s put these concepts into practice.
Example 1: Creating a View
The below snippet shows the content of the base table:
Suppose we want to create a view from the “staff_info” table. For this purpose, we will use the following statement:
CREATE VIEW staff_view AS SELECT staff_id, staff_name, staff_designation FROM staff_info WHERE staff_id <= 5;
The “CREATE VIEW” message in the output window demonstrates that the “staff_view” has been created. Let’s verify it via the “SELECT *” command:
SELECT * FROM staff_view;
Example 2: Creating an Already Existing View
Trying to create an already existing view will throw an error, as shown in the following snippet:
To avoid this error, you need to execute the “CREATE VIEW” statement with the “OR REPLACE” parameter:
CREATE OR REPLACE VIEW staff_view AS SELECT staff_id, staff_name, staff_designation FROM staff_info WHERE staff_id <= 8;
The output shows that this time the CREATE VIEW command executed successfully. To verify the view’s creation/modification, you must execute the following command:
SELECT * FROM staff_view;
The “staff_view” with desired records has been created successfully.
Example 3: Creating/Defining a View From Multiple Postgres Tables
We have two sample tables named “employee_info” and “department_info”. The below snippet demonstrates the content of the “emp_info” table:
The content of the “department_info” table is shown in the following snippet:
Let’s learn how to create a view from multiple tables:
CREATE VIEW emp_data_view AS SELECT employee_info.e_id, e_name, dpt_name FROM employee_info INNER JOIN department_info ON employee_info.e_id = department_info.e_id;
In the above snippet, the INNER JOIN is used with the CREATE VIEW statement to create a view from multiple tables:
The “CREATE VIEW” message in the output window signifies that the desired view has been created from multiple tables. Let’s execute the “SELECT *” command to fetch the data from the “emp_view_data”:
This way, you can use the CREATE VIEW statement to create/define a view from one or more tables.
In PostgreSQL, the CREATE VIEW statement defines a new view based on the selected table(s). To create a view from several tables, use the CREATE VIEW statement with INNER JOIN. Creating an existing view will throw a “relation already exists” error. To avoid such an error, use the “OR REPLACE” parameter with the CREATE VIEW statement. This Postgre blog presented different examples to illustrate how to create a view in Postgres.