How to create, modify, delete, and manage views in PostgreSQL

December 17, 2019

SUMMARY: This article discusses VIEW in PostgreSQL. It describes how a VIEW functions as a shortcut if calling the same query multiple times, then defines the following commands:

1. CREATE VIEW

2. ALTER VIEW

3. DROP VIEW

 

PostgreSQL’s VIEW is a versatile tool for “viewing” the data in a database. A VIEW is a query that you give a name to so that you can reference it just like you would a table. There are also MATERIALIZED VIEWs, which are similar but slightly different, and we cover that here. A VIEW doesn’t replace a table—VIEWs require tables to pull information from them. However, once those tables are in place, you can use VIEWs to examine and use those tables’ data. This can be useful for a number of situations. For example, if there’s a query that you run really often, and you don’t want to keep typing it, you can use a VIEW. Here, I have an example of a situation where I have two tables that I pull information from, “customer_table” and “city_table.”

postgres=# select first_name, last_name, email, city from customer_table, city_table where city=’Bedford’; 

 

If this is a query that is run often, or we want to run a query that involves information that this query already involves, we can create a VIEW with this query:

create view my_view as select first_name, last_name, email, city from customer_table, city_table where city=’Bedford’; 

 

Then in the future, we can just call the VIEW itself:

postgres=# select * from my_view;

 

Once we’ve created a VIEW, we can look at the details of that view using the \d+ command:

postgres=# \d+ my_view

                                    View "public.my_view"

   Column   |         Type          | Collation | Nullable | Default | Storage  | Description

------------+-----------------------+-----------+----------+---------+----------+-------------

 first_name | character varying(50) |           |          |         | extended |

 last_name  | character varying(50) |           |          |         | extended |

 email      | character varying(50) |           |          |         | extended |

 city       | character varying(50) |           |          |         | extended |

View definition:

 SELECT customer_table.first_name,

    customer_table.last_name,

    customer_table.email,

    city_table.city

   FROM customer_table,

    city_table

  WHERE city_table.city::text = ’Bedford’::text;

 

We can make VIEWs that pull information from other VIEWs as well, giving you even more flexibility over how you want to access your data.

 

CREATE VIEW

If you ever need to change an existing VIEW, the command you use depends on what you want to change. If you want to change the query that is being used, you can use a modified version of the CREATE command:

postgres=# create or replace view my_view as select first_name, last_name, email, city, country from customer, city

where city=’Bedford’;

CREATE VIEW



postgres=# \d+ my_view

                                    View "public.my_view"

   Column   |         Type          | Collation | Nullable | Default | Storage  | Description

------------+-----------------------+-----------+----------+---------+----------+-------------

 first_name | character varying(50) |           |          |         | extended |

 last_name  | character varying(50) |           |          |         | extended |

 email      | character varying(50) |           |          |         | extended |

 city       | character varying(50) |           |          |         | extended |

 country      | character varying(50) |           |          |         | extended |

View definition:

 SELECT customer_table.first_name,

    customer_table.last_name,

    customer_table.email,

    city_table.city

    city_table.country

   FROM customer_table,

    city_table

  WHERE city_table.city::text = ’Bedford’::text;

 

ALTER VIEW

On the other hand, if you want to change the properties of the VIEW, such as the owner, or rename the VIEW, you can use the ALTER VIEW command.

postgres=# alter view my_view rename to customers_bedford;

ALTER VIEW

 

One way to manage your VIEWs is to use “pg_views.” This will allow you to see all of the VIEWs that currently exist, who created them, the name of the VIEW, the schema they are in, and the definition.

postgres=# select * from pg_views; 

 schemaname |     viewname      | viewowner |                  definition

------------+-------------------+-----------+-----------------------------------------------

 public     | customers_bedford | postgres  |  SELECT customer_table.first_name,                 +

            |                   |           |     customer_table.last_name,                      +

            |                   |           |     customer_table.email,                          +

            |                   |           |     city_table.city,                               +

            |                   |           |     city_table.country                           +

            |                   |           |    FROM customer_table,                            +

            |                   |           |     city_table                                    +

            |                   |           |   WHERE ((city_table.city)::text = ‘Bedford’::text);



…



 pg_catalog  | session_wait_history  | enterprisedb |  SELECT t1.backend_id,           +

                    |                                       |              |     t1.seq,                                             +

                    |                                       |              |     t1.wait_name,        +

                    |                                       |              |     t1.elapsed,+

                    |                                       |              |     t1.p1,                  +

                    |                                       |              |     t1.p2, +

                    |                                       |              |     t1.p3 +

                    |                                       |              |    FROM edb_rita_session_wait_history() t1(backend_id bigint, seq bigint, wait_name text, elapsed bigint, p1 bigint, p2 bigint, p3 bigint);

(226 rows)

 

DROP VIEW

Finally, if you decide you no longer need a VIEW, simply use the DROP VIEW command to remove it.

postgres=# drop view customers_bedford;

DROP VIEW

 

If you have more questions about VIEWs, please feel free to let us know!