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.

Conclusion

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.