# VIEWS in PostgreSQL: CREATE VIEW, DROP VIEW, UPDATE VIEW

> Postgres provides the “CREATE VIEW”, “CREATE OR REPLACE VIEW”, and “DROP VIEW” statements to create, update or drop a view from the database.

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.

---
[View this page online](https://www.commandprompt.com/education/views-in-postgresql-create-view-drop-view-update-view/)