Views

This section is a brief introduction to database views and their management, including creating, modifying, and removing them from a database.

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. What views enable is for the user to convert the following complex query…

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

…into this far simpler one.

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;

Content for this note is taken from information in “The Complete SQL Bootcamp” course on Udemy. I highly recommend it.