PostgreSQL View

Summary: in this tutorial, you’ll learn about PostgreSQL view and how to use it to simplify complex queries.

Getting Started with the PostgreSQL View #

A view is a named query stored in the PostgreSQL database server. A view allows you to query data from it like a regular table but does not store the data physically. Therefore, a view is referred to as a virtual table.

In PostgreSQL, you can create views that store data physically. These views are called materialized views.

Creating a view #

To create a view, you start with a query that returns a result set and assign it a name.

In PostgreSQL, you use the CREATE VIEW statement to create a new view:

CREATE VIEW view_name 
AS 
query;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

• First, specify the view name after the CREATE VIEW keywords.
• Second, provide a query that defines the view.

The query that defines the view is called the defining query. The tables that the defining query references are called base tables.

The following statement creates a view called product_view based on a query that selects data from the products and categories tables:

CREATE VIEW product_view 
AS
SELECT
  product_id,
  product_name,
  price,
  category_name
FROM
  products
  JOIN categories USING (category_id);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

After creating the product_view view, you can query the data from it.

Querying data using views #

The following example uses the SELECT statement to query data from the based tables via the product_view:

SELECT
  product_name,
  category_name,
  price
FROM
  product_view;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

In this query, we use the product_view like a regular table. When PostgreSQL receives the query, it does the following steps:

• First, execute the defining query of the product view.
• Second, select data from the result set returned by the defining query.

Technically, it’ll execute the following query:

SELECT
  product_name,
  brand_name,
  price
FROM
  (
    SELECT
      product_id,
      product_name,
      price,
      category_name
    FROM
      products
      JOIN categories USING (category_id)
  ) t;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

In this example, we use the product_view to simplify a query that includes joins. Additionally, we can reuse the product_view in other queries.

For example, we can retrieve categories and the most expensive product price in each by querying from the product_view as follows:

SELECT
  category_name,
  MAX(price) price
FROM
  product_view
GROUP BY
  category_name
ORDER BY
  price;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

This query is more simple than the one that uses joins:

SELECT
  category_name,
  MAX(price) price
FROM
  products
  JOIN categories USING (category_id)
GROUP BY
  category_name
ORDER BY
  price;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Replacing views #

If you want to modify an existing view, you can use the OR REPLACE clause in the CREATE VIEW statement:

CREATE OR REPLACE VIEW view_name 
AS query;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The CREATE OR REPLACE VIEW will create the view_name if it does not exist or replace the existing one if it does.

The following example replaces the product_view by adding the brand_name column from the brands table:

CREATE OR REPLACE TABLE product_view AS
SELECT
  product_id,
  product_name,
  price,
  category_name,
  brand_name
FROM
  products
  JOIN categories USING (category_id)
  JOIN brands USING (brand_id);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Creating a view based on other views #

PostgreSQL allows you to create a view based on other views. For example, the following creates a view called smartphone_view based on the product_view:

CREATE OR REPLACE VIEW smartphone_view AS
SELECT
  product_id,
  product_name,
  price,
  brand_name
FROM
  product_view
WHERE
  category_name = 'Smartphones';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Removing a view #

The DROP VIEW allows you to delete a view from the database permanently:

DROP VIEW IF EXISTS view_name;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

PostgreSQL does not allow you to drop a view that has another view depending on it. To drop a view that has dependencies, you can use the CASCADE option:

DROP VIEW IF EXISTS view_name CASCADE;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The CASCADE option will drop the views (and other objects) that depend on the view_name and, in turn, all objects that depend on those objects.

For example, the following statement drops the product_view and its dependent views:

DROP VIEW IF EXISTS product_view CASCADE;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Summary #

• A view is a named query stored in the PostgreSQL database server.
• A view can help simplify a complex query.

Quiz #