Summary: in this tutorial, you will learn about views and how to manage views in PostgreSQL.
A view is a database object that is of a stored query. A view can be accessible as a virtual table in PostgreSQL. In other words, a PostgreSQL view is a logical table that represents data of one or more underlying tables through a SELECT statement. Notice that a view does not store data physically except for a materialized view.
A view can be very useful in some cases such as:
- A view helps simplify the complexity of a query because you can query a view, which is based on a complex query, using a simple
SELECTstatement. - Like a table, you can grant permission to users through a view that contains specific data that the users are authorized to see.
- A view provides a consistent layer even the columns of underlying table changes.
Creating PostgreSQL Views
To create a view, we use CREATE VIEW statement. The simplest syntax of the CREATE VIEW statement is as follows:
1 | CREATE VIEW view_name AS query; |
First, you specify the name of the view after the CREATE VIEW clause, then you put a query after the AS keyword. A query can be a simple SELECT statement or a complex SELECT statement with joins.
PostgreSQL CREATE VIEW example
For example, in our sample database, we have four tables:
-
customer– stores all customer data -
address– stores address of customers -
city– stores city data -
country– stores country data
If you want to get a complete customers data, you normally construct a join statement as follows:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | SELECT cu.customer_id AS id, (((cu.first_name)::text || ' '::text) || (cu.last_name)::text) AS name, a.address, a.postal_code AS "zip code", a.phone, city.city, country.country, CASE WHEN cu.activebool THEN 'active'::text ELSE ''::text END AS notes, cu.store_id AS sid FROM (((customer cu JOIN address a ON ((cu.address_id = a.address_id))) JOIN city ON ((a.city_id = city.city_id))) JOIN country ON ((city.country_id = country.country_id))); |
The result of the query is as shown in the screenshot below:
This query is quite complex. However, you can create a view named customer_master as follows:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | CREATE VIEW customer_master AS SELECT cu.customer_id AS id, (((cu.first_name)::text || ' '::text) || (cu.last_name)::text) AS name, a.address, a.postal_code AS "zip code", a.phone, city.city, country.country, CASE WHEN cu.activebool THEN 'active'::text ELSE ''::text END AS notes, cu.store_id AS sid FROM (((customer cu JOIN address a ON ((cu.address_id = a.address_id))) JOIN city ON ((a.city_id = city.city_id))) JOIN country ON ((city.country_id = country.country_id))); |
From now on, whenever you need to get a complete customer data, you just query it from the view by executing the following simple SELECT statement:
1 2 3 4 | SELECT * FROM customer_master; |
This query produces the same result as the complex one with joins above.
Changing PostgreSQL Views
To change the defining query of a view, you use the CREATE VIEW statement with OR REPLACE addition as follows:
1 2 3 | CREATE OR REPLACE view_name AS query |
PostgreSQL does not support removing an existing column in the view, at least up to version 9.4. If you try to do it, you will get an error message: “[Err] ERROR: cannot drop columns from view”. The query must generate the same columns that were generated when the view was created. To be more specific, the new columns must have the same names, same data types, and in the same order as they were created. However, PostgreSQL allows you to append additional columns at the end of the column list.
For example, you can add an email to the customer_master view as follows:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | CREATE OR REPLACE VIEW customer_master AS SELECT cu.customer_id AS id, (((cu.first_name)::text || ' '::text) || (cu.last_name)::text) AS name, a.address, a.postal_code AS "zip code", a.phone, city.city, country.country, CASE WHEN cu.activebool THEN 'active'::text ELSE ''::text END AS notes, cu.store_id AS sid, cu.email FROM (((customer cu JOIN address a ON ((cu.address_id = a.address_id))) JOIN city ON ((a.city_id = city.city_id))) JOIN country ON ((city.country_id = country.country_id))); |
Now, if you select data from the customer_master view, you will see the email column at the end of the list.
1 2 3 4 | SELECT * FROM customer_master; |
To change the definition of a view, you use the ALTER VIEW statement. For example, you can change the name of the view from customer_master to customer_info by using the following statement:
1 | ALTER VIEW customer_master RENAME TO customer_info; |
PosgreSQL allows you to set a default value for a column name, change the view’s schema, set or reset options of a view. For detailed information on the altering view’s definition, check it out the PostgreSQL ALTER VIEW statement.
Removing PostgreSQL Views
To remove an existing view in PosgreSQL, you use DROP VIEW statement as follows:
1 | DROP VIEW [ IF EXISTS ] view_name; |
You specify the name of the view that you want to remove after DROP VIEW clause. Removing a view that does not exist in the database will result an error. To avoid this, you normally add IF EXISTS option to the statement to instruct PostgreSQL to remove the view if it exists, otherwise do nothing.
For example, to remove the customer_info view that you have created, you execute the following query:
1 | DROP VIEW IF EXISTS customer_info; |
The view customer_infois removed from the database.
In this tutorial, we have shown you how to create, alter, and remove PostgreSQL views.