A PostgreSQL view is a virtual table created based on the SELECT command. It does not physically store data but rather displays the results of a SELECT statement every time it is queried.
A view can simplify queries, join multiple tables together, perform calculations, and return the results. It can also be used to provide users with a simplified version of the data in the underlying tables or restrict the visible data to the user.
This write-up will use suitable examples to demonstrate how to create, drop, or update a view in Postgres. So, let’s start!
What is a VIEW in PostgreSQL?
In Postgres, a view is a virtual table representing data of one or more underlying tables using the SELECT command. Once a view is created, you can select data from it the same way as you select from a real/ordinary table.
Why Should Someone Use the VIEWS in Postgres?
Views are useful for many reasons, some of which are listed below:
- VIEWS in Postgres allows us to encapsulate complex SELECT statements and present the data to users in a simpler way.
- A VIEW can be created only to display a subset of the data in a table, thereby limiting access to the data.
- VIEWS can be used to simplify the structure of a database by abstracting complex queries into simpler views.
How to Create a VIEW in Postgres?
Here is the basic syntax for creating a view in Postgres:
CREATE <OR REPLACE> VIEW view_name AS SELECT col_list FROM tab_name WHERE condition;
In the above snippet, the “OR REPLACE” is an optional clause used to avoid the “VIEW already exists” error.
Example: How Does the CREATE VIEW Command Work in Postgres?
Let’s create a view(virtual table) named “example_view” based on the result set of the SELECT statement:
CREATE VIEW example_view AS SELECT st_id, st_name, st_phone, st_email FROM staff_data;
The output snippet clarifies that a view named example_view has been created successfully. To query the newly created view, you must execute the following command:
SELECT * FROM example_view;
The output verifies the working of the CREATE VIEW statement.
How to Update/Modify a VIEW in Postgres?
To update/alter the definition of an already existing view, use the “CREATE OR REPLACE VIEW” command. Here is the basic syntax for updating a view in Postgres:
CREATE OR REPLACE VIEW view_name AS SELECT col_list FROM tab WHERE conditions;
This statement assists the users in modifying the view’s definition without dropping it.
Example: How to Alter a View in Postgres?
Let’s learn how to modify the definition of an already existing view in Postgres:
CREATE OR REPLACE VIEW example_view AS SELECT st_id, st_name, st_phone, st_email FROM staff_data WHERE st_id > 1;
The above snippet shows that the “CREATE OR REPLACE VIEW” statement was executed successfully. Let’s execute the “SELECT *” command to query the updated view:
SELECT * FROM example_view;
The output shows that the example_view has been updated successfully.
How to Drop a VIEW in Postgres?
To use the DROP VIEW statement in Postgres, users must follow the below syntax:
DROP VIEW <IF EXISTS> view_name;
The “IF EXISTS” is an optional clause used to avoid the view does not exist error.
Example: How Does the DROP VIEW Statement Work in Postgres?
Execute the below-provided statement to delete a view named “example_view”:
DROP VIEW example_view;
The output shows that the targeted view has been dropped successfully.
In PostgreSQL, a view is a virtual table that is created based on a SELECT query. Postgres provides the “CREATE VIEW”, “CREATE OR REPLACE VIEW”, and “DROP VIEW” statements to create, update or drop a view from the database. The WHERE clause can be used with these statements to create, update, or delete a view based on some specific condition. This Postgres blog demonstrated various examples to explain the working of the “CREATE VIEW”, “CREATE OR REPLACE VIEW”, and “DROP VIEW” statements.