PostgreSQL Tutorial PostgreSQL Advanced PostgreSQL Database Account Management PostgreSQL References
PostgreSQL Tutorial PostgreSQL Advanced PostgreSQL Database Account Management PostgreSQL References

PostgreSQL - VIEWS



The PostgreSQL VIEW statement is used to create a PostgreSQL VIEW which is a virtual table created based on the PostgreSQL statement. A view contains rows and columns just like a normal table. All PostgreSQL functions, WHERE, HAVING and JOINs statements can be used to create a PostgreSQL VIEW. The PostgreSQL VIEW statement can be used to create, update or delete a view.

Syntax

The syntax for using VIEW statement in PostgreSQL is given below:

/* Create a view */
CREATE VIEW view_name AS
SELECT column1, column2, column3, ...
FROM table_name
WHERE condition(s);

/* Update a view */
CREATE OR REPLACE VIEW view_name AS
SELECT column1, column2, column3, ...
FROM table_name
WHERE condition(s);

/* Drop a view */
DROP VIEW view_name;

Example:

Consider a database table called Employee with the following records:

EmpIDNameCityAgeSalary
1JohnLondon253000
2MarryNew York242750
3JoParis272800
4KimAmsterdam303100
5RameshNew Delhi283000
6HuangBeijing282800

CREATE VIEW

The below mentioned PostgreSQL statement is used to create a view on Employee table which contains all records of employee whose salary is greater than 2800.

CREATE VIEW Employee_Salary_GT_2800 AS
SELECT * FROM Employee WHERE Salary > 2800;

Query the VIEW

After creating the VIEW, it can be used as mentioned below:

SELECT * FROM Employee_Salary_GT_2800;

This will produce the result as shown below:

EmpIDNameCityAgeSalary
1JohnLondon253000
4KimAmsterdam303100
5RameshNew Delhi283000

CREATE OR REPLACE VIEW

The CREATE OR REPLACE VIEW statement is used to update the view if it exists otherwise creates a new view.

The mentioned below statement is used to update the above view which contains all records of employee whose salary is greater than 2800 and age is greater than 25.

CREATE OR REPLACE VIEW Employee_Salary_GT_2800 AS
SELECT * FROM Employee WHERE Salary > 2800 AND AGE > 25;

After updating the VIEW, the following query can be used to see its content:

SELECT * FROM Employee_Salary_GT_2800;

This will produce the result as shown below:

EmpIDNameCityAgeSalary
4KimAmsterdam303100
5RameshNew Delhi283000

DROP VIEW

The DROP VIEW is used to delete a view.

DROP VIEW Employee_Salary_GT_2800;