PostgreSQL: VIEW
This PostgreSQL tutorial explains how to create, update, and drop VIEWS in PostgreSQL with syntax and examples.
What is a VIEW in PostgreSQL?
In PostgreSQL, a VIEW is not a physical table, but rather, it is in essence a virtual table created by a query joining one or more tables.
Create VIEW
Syntax
The syntax for the CREATE VIEW statement in PostgreSQL is:
CREATE [OR REPLACE] VIEW view_name AS SELECT columns FROM tables [WHERE conditions];
- OR REPLACE
- Optional. If you do not specify this clause and the VIEW already exists, the CREATE VIEW statement will return an error.
- view_name
- The name of the VIEW that you wish to create in PostgreSQL.
- WHERE conditions
- Optional. The conditions that must be met for the records to be included in the VIEW.
Example
Here is an example of how to use the CREATE VIEW statement to create a view in PostgreSQL:
CREATE VIEW current_inventory AS SELECT product_name, quantity FROM products WHERE quantity > 0;
This CREATE VIEW example would create a virtual table based on the result set of the SELECT statement. You can now query the PostgreSQL VIEW as follows:
SELECT * FROM current_inventory;
Update VIEW
You can modify the definition of a VIEW in PostgreSQL without dropping it by using the CREATE OR REPLACE VIEW Statement.
Syntax
The syntax for the CREATE OR REPLACE VIEW statement in PostgreSQL is:
CREATE OR REPLACE VIEW view_name AS SELECT columns FROM table WHERE conditions;
- view_name
- The name of the view that you wish to update.
Example
Here is an example of how you would use the CREATE OR REPLACE VIEW statement in PostgreSQL:
CREATE or REPLACE VIEW current_inventory AS SELECT product_name, quantity, category_name FROM products INNER JOIN categories ON products.category_id = categories.category_id WHERE quantity > 0;
This CREATE OR REPLACE VIEW example would update the definition of the VIEW called current_inventory without dropping it.
CAUTION:
The CREATE OR REPLACE VIEW statement will work if you are adding columns to the view at the end of the list. However, it will error if you are adding new columns within the existing columns (ie: start or middle of the existing list).
In this case, do not use the CREATE OR REPLACE VIEW statement. It is better to drop the view and use the CREATE VIEW statement!
Drop VIEW
Once a VIEW has been created in PostgreSQL, you can drop it with the DROP VIEW statement.
Syntax
The syntax for the DROP VIEW statement in PostgreSQL is:
DROP VIEW [IF EXISTS] view_name;
- view_name
- The name of the view that you wish to drop.
- IF EXISTS
- Optional. If you do not specify this clause and the VIEW does not exist, the DROP VIEW statement will return an error.
Example
Here is an example of how to use the DROP VIEW statement in PostgreSQL:
DROP VIEW current_inventory;
This DROP VIEW example would drop/delete the PostgreSQL VIEW called current_inventory.
Advertisements