PostgreSQL VIEW: how to create, update, and drop

Preamble

You will learn how to create, update, and drop VIEWS in PostgreSQL with syntax and examples.

What is PostgreSQL VIEW?

In PostgreSQL, VIEW is not a physical table, but rather a virtual table created by a joins query that connects one or more tables.

Create VIEW

Syntax of CREATE VIEW operator in PostgreSQL

CREATE [OR REPLACE] VIEW view_name AS
SELECT columns
FROM tables
[WHERE conditions];

• OR REPLACE – Optional. If you do not specify this operator and VIEW already exists, the CREATE VIEW operator will return the error.
• view_name – The VIEW name you want to create in PostgreSQL.
• WHERE conditions – Optional. The conditions that must be met to enable entries in VIEW.

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 example CREATE VIEW will create a virtual table based on the resulting SELECT operator set. You can now query the PostgreSQL VIEW as follows:

SELECT *
FROM current_inventory;

Update VIEW

You can change the VIEW definition in PostgreSQL without removing it using the CREATE OR REPLACE VIEW statement.

The syntax for CREATE OR REPLACE VIEW in PostgreSQL

CREATE OR REPLACE VIEW view_name AS
SELECT columns
FROM table
WHERE conditions;

• view_name – The name of the view you want to update.

An example of how the CREATE OR REPLACE VIEW operator can be used 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 VIEW definition with the name current_inventory without removing it.

WARNING: THIS EXAMPLE WOULD UPDATE THE DEFINITION OF VIEW WITH THE NAME CURRENT_INVENTORY WITHOUT REMOVING IT

The CREATE OR REPLACE VIEW statement will work if you add columns to the view at the end of the list. However, it will be an error if you add new columns to the existing columns (i.e.: start or middle of an existing list).

In this case, do not use the CREATE OR REPLACE VIEW statement. It is better to delete VIEW and use the CREATE VIEW operator!

Drop VIEW

After creating VIEW in PostgreSQL, you can delete it using the Drop VIEW operator.

The syntax for Drop VIEW in PostgreSQL

Drop VIEW [IF EXISTS] view_name;

• view_name – The name of the view you want to delete.
• IF EXISTS – Optional. If you do not specify this option and VIEW does not exist, Drop VIEW will return the error.

An example of how to use the Drop VIEW operator in PostgreSQL

Drop VIEW current_inventory;

This example will delete a VIEW named current_inventory.

Postgres database views

About Enteros

IT organizations routinely spend days and weeks troubleshooting production database performance issues across multitudes of critical business systems. Fast and reliable resolution of database performance problems by Enteros enables businesses to generate and save millions of direct revenue, minimize waste of employees’ productivity, reduce the number of licenses, servers, and cloud resources and maximize the productivity of the application, database, and IT operations teams.