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!