Postgres Create View

Updated May 18, 2023

Introduction to Postgres Create View

Views are the pseudo-tables that, in reality, are not materialized. The records of view do not actually consume any memory in the physical database. Views are created by utilizing existing temporary or permanent tables. The columns are combined or are specified from the existing tables while view creation which is, in turn, called as the base tables. Views created based on temporary tables are classified as temporary themselves. Upon closing the associated session, the system automatically drops these views. When referencing a view internally, the system executes a query on the base tables to retrieve the records of the view.

In this article, we will learn about how to create views and change or replace them and fetch the records from the view, and finally, how to delete the view using the DROP command. All the permission allocation, ownership rules, and manipulations of the views in PostgreSQL are the same and remain unchanged. It is important to note that views do not allow updation, insertion, or deletion operations. However, we can internally write the triggers on the view, where we can write the queries for changing the base tables whenever the corresponding change is done on the view. You can create views from one or more base tables, and they can contain one or more columns. We can retrieve the records from the view in the same fashion as we do for the tables. We will begin by learning the syntax to create the view in PostgreSQL.

Syntax:

CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] VIEW nameOfView [ ( nameOfColumn [, ...] ) ]
[ WITH ( view_option_name [= view_option_value] [, ... ] ) ]
AS querySpecifyingRecords

Parameters:

• TEMPORARY or TEMP – You have the option to include the optional statement “TEMPORARY” or “TEMP” in the query if you wish to declare the target view as a temporary view. By default, the views are permanent in nature. Creating views using temporary base tables automatically designates them as temporary, regardless of whether it is explicitly stated in the query. Otherwise, they are permanent in nature until and unless specified in the query.
• NameOfView – You use the “NameOfView” to specify the desired name for the target view you want to create.
• NameOfColumn – These are optional column names that can be specified for preparing the view that will contain columns named as specified. If the “NameOfView” is not explicitly specified, the system deduces it from the querySpecifyingRecords query, which refers to the base tables.
• view_option_name – We can provide any optional parameters while creating the view using this syntax. For now, only one parameter type is available to pass, named security_barrier, which helps provide row-level security to the view when enabled.
• QuerySpecifyingRecords – The “QuerySpecifyingRecords” can take the form of either a SELECT query or a VALUES clause. This helps in specifying the rows and columns that should be inserted into the view.

Example of Postgres Create View

To create the view, we need to have the base tables. Let us check by typing the command \dt on the psql prompt. As can be seen, only one table named educba exists in my postgres database. Let us create two tables named teams and developers and insert some values in both of them.

CREATE TABLE teams (
id SERIAL PRIMARY KEY,
team_count INTEGER,
department VARCHAR (100)
);

Now, we will create a table for developers that will act as the referencing or child table. There will be one to many relationships between teams and developers’ tables. team_id will be our referencing key, which will refer to the id of the team’s table.

CREATE TABLE developers (
developer_id INTEGER NOT NULL,
team_id INTEGER REFERENCES teams (id),
name VARCHAR (100),
position VARCHAR (100),
technology VARCHAR (100),
PRIMARY KEY (developer_id,team_id)
);

INSERT INTO teams (id, team_count, department) VALUES('1','5','Accounting');
INSERT INTO teams (id, team_count, department) VALUES('2','6','Inventory');
INSERT INTO teams (id, team_count, department) VALUES('3','5','Human Resource');
INSERT INTO teams (id, team_count, department) VALUES('4','7','CRM');
INSERT INTO teams (id, team_count, department) VALUES('5','9','Bug Solver');
INSERT INTO teams (id, team_count, department) VALUES('6','4','Document');
INSERT INTO developers (developer_id, team_id, name, position, technology) VALUES(1,2,'Payal','senior SD','Java');
INSERT INTO developers (developer_id, team_id, name, position, technology) VALUES(2,1,'Heena','Developer','Angular');
INSERT INTO developers (developer_id, team_id, name, position, technology) VALUES(3,2,'Sayali','Developer','Hibernate');
INSERT INTO developers (developer_id, team_id, name, position, technology) VALUES(4,3,'Rahul','Support','Digital Marketing');
INSERT INTO developers (developer_id, team_id, name, position, technology) VALUES(5,3,'Siddhesh','Tester','Maven');

as shown below –

Now, let us check the contents of both tables teams and developers using query statements –

SELECT * FROM teams;

SELECT * FROM developers;

Firstly we will create a simple view which will retrieve the records from one table only for developers with name and position columns with records whose team is “Inventory,” means its team id is 2. For doing this, our query statement will be as follows –

CREATE VIEW inv_team AS SELECT name,position FROM developers WHERE team_id = 2;

which will result into the following output. To verify our view’s contents, let us check them by firing the query

SELECT * FROM inv_team;

Note: After closing the session and reopening the psql prompt, executing the select query successfully retrieved the correct records. This indicates that the view we created is permanent, as we did not specify TEMP or TEMPORARY in the create view query.

Now, we will create a temporary view that will contain the name of the developer and its team’s department. That means there will be two base tables. Let us begin by framing or query statements.

CREATE or REPLACE TEMPORARY VIEW team_details AS SELECT a.department, b.name FROM teams a INNER JOIN developers b ON a.id = b.team_id;

which results in the following output –

Let us check its contents using the query

SELECT * FROM team_details;

Now, since it is a temporary view, let’s close our current session and verify if we obtain the same output after starting a new session.

As the session, where the temporary table and the view were deleted, has been closed, the team_details view is no longer accessible.

Now, let us check the contents of the inv_team view in this new session.

SELECT * FROM inv_team;

To delete this view, we will need to use the DROP VIEW command as follows –

DROP VIEW inv_team;

and check its contents by using the SELECT statement as above, which gives output –

So, the view is deleted permanently.

Conclusion

In PostgreSQL, you can create views on one or more base tables with one or more columns, which can be either temporary or permanent. To completely remove a view, you use the DROP VIEW command.

We hope that this EDUCBA information on “Postgres Create View” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

1. PostgreSQL age()
2. PostgreSQL SET
3. Integer PostgreSQL
4. PostgreSQL NOT IN