PostgreSQL Create And Drop Views

The PostgreSQL Views are the definitions that are stored in the dictionary tables of the system. The Views are not materialized and hence every time the query runs on views and it is refreshed and fetches the records and shares them with the client. To create a view we can use the CREATE VIEW statement and we should have create view permission present on the object as well. The CREATE OR REPLACE VIEW statement is also the same but if there is a view already then this command will replace the existing view.

The following is the syntax of PostgreSQL View.


Syntax:

CREATE OR replace [ TEMP | TEMPORARY ] VIEW viewname  ASSELECT column1,column2,column2...from TABLENAME WHERE (condition);

1. Create View Using Psql

The CREATE OR REPLACE VIEW command is used to create a new view or replace an existing view.

In this section, we will create a view named PRODUCTS that will be based on the base table named PRODUCTS_DETAIL. The view will contain only two columns PRODUCT_NUMBER, PRODUCT_NAME where the PRODUCT_PRICE>=5.


Command:

postgres=# CREATE VIEW products AS SELECT product_number,product_name FROM products_detail WHERE product_price>=5;


Output:

In the below output, we can see the new view has been created with the name PRODUCTS and contain the following records.


postgresql create view command


2. Drop View Using Psql

The DROP VIEW command is used to drop an existing view. To drop view, we should have the Drop View permission should have been granted on it.

In the below section, we will drop this view PRODUCTS that was created in the create view section.

The syntax of the Drop View statement is as mentioned below.


Syntax:

DROP VIEW [ IF EXISTS ] view_name [cascade];

Command:

postgres=# DROP VIEW products;


Output:

The view with the name PRODUCTS has been dropped.


postgresql drop view command