PostgreSQL: VIEW

In this PostgreSQL post 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.

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.