VIEW

This PostgreSQL tutorial explains how to create, update, and drop VIEWS in PostgreSQL with syntax and examples.

What is a VIEW in PostgreSQL?

In PostgreSQL, a VIEW is not a physical table, but rather, it is in essence a virtual table created by a query joining one or more tables.

Create VIEW

Syntax

The syntax for the CREATE VIEW statement in PostgreSQL is:

CREATE [OR REPLACE] VIEW view_name AS

  SELECT columns

  FROM tables

  [WHERE conditions];

OR REPLACE

Optional. If you do not specify this clause and the VIEW already exists, the CREATE VIEW statement will return an error.

view_name

The name of the VIEW that you wish to create in PostgreSQL.

WHERE conditions

Optional. The conditions that must be met for the records to be included in the VIEW.

Example

Here is 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 CREATE VIEW example would create a virtual table based on the result set of the SELECT statement. You can now query the PostgreSQL VIEW as follows:

SELECT *

FROM current_inventory;

Update VIEW

You can modify the definition of a VIEW in PostgreSQL without dropping it by using the CREATE OR REPLACE VIEW Statement.

Syntax

The syntax for the CREATE OR REPLACE VIEW statement in PostgreSQL is:

CREATE OR REPLACE VIEW view_name AS

  SELECT columns

  FROM table

  WHERE conditions;

view_name

The name of the view that you wish to update.

Example

Here is an example of how you would use the CREATE OR REPLACE VIEW statement 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 definition of the VIEW called current_inventory without dropping it.

CAUTION:

The CREATE OR REPLACE VIEW statement will work if you are adding columns to the view at the end of the list. However, it will error if you are adding new columns within the existing columns (ie: start or middle of the existing list).

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

Drop VIEW

Once a VIEW has been created in PostgreSQL, you can drop it with the DROP VIEW statement.

Syntax

The syntax for the DROP VIEW statement in PostgreSQL is:

DROP VIEW [IF EXISTS] view_name;

view_name

The name of the view that you wish to drop.

IF EXISTS

Optional. If you do not specify this clause and the VIEW does not exist, the DROP VIEW statement will return an error.

Example

Here is an example of how to use the DROP VIEW statement in PostgreSQL:

DROP VIEW current_inventory;

This DROP VIEW example would drop/delete the PostgreSQL VIEW called current_inventory.