EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials PostgreSQL Tutorial Postgres Create View
 

Postgres Create View

Payal Udhani
Article byPayal Udhani
EDUCBA
Reviewed byRavi Rathore

Updated May 18, 2023

Postgres Create View

 

 

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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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 –

Postgres Create View 1

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

SELECT * FROM teams;

Postgres Create View 2

SELECT * FROM developers;

Postgres Create View 3

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;

Postgres Create View 4

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;

Postgres Create View 5

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 –

Postgres Create View 6

Let us check its contents using the query

SELECT * FROM team_details;

Select 1

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.

Select 2

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;

Select 3

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 –

Select 4

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.

Recommended Articles

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

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW