Views

View Basics

A view is a database object that can usually be accessed as a virtual table, but which comes from a stored query. A PostgreSQL view is a logical table that represents data of underlying table(s) via a SELECT statement.

Views do not store data physically. Views enable consolidation of queries that involve multiple tables, like the first table below, into the simplified second table.

SELECT c1, c2, c3, c4, c5, c6
  FROM t1
  JOIN t2 USING (c1)
  JOIN t3 USING (c2);

SELECT *
  FROM view;

Views:

• help simplify query complexity because you can query a view using a simple SELECT
• can have permissions assigned
• are a consistent layer, even if the underlying data changes

View Creation

To create a view, use CREATE VIEW:

CREATE VIEW view_name AS query;

An example:

CREATE VIEW customer_info AS
SELECT first_name,
       last_name,
       email,
       address,
       phone
  FROM customer
  JOIN address
    ON customer.address_id = address.address_id;

Then, the following much simpler query can be run in lieu of the longer query above. As suggested by the syntax below, ‘customer_info’ can be thought of as a virtual table.

SELECT *
  FROM customer_info;

Alter View

The following command allows users to rename views.

 ALTER VIEW customer_info
RENAME TO customer_master_list;

Following that command, the following can be run.

SELECT *
  FROM customer_master_list

Remove View

IF EXISTS, in the following query, is optional.

DROP VIEW IF EXISTS customer_master_list;