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
Views do not store data physically. Views enable consolidation of queries that involve multiple tables, like the first table below, into the simplified second table.
SELECT c1, c2, c3, c4, c5, c6 FROM t1 JOIN t2 USING (c1) JOIN t3 USING (c2);
SELECT * FROM view;
- help simplify query complexity because you can query a view using a simple
- can have permissions assigned
- are a consistent layer, even if the underlying data changes
To create a view, use
CREATE VIEW view_name AS query;
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;
|Jared||Ely||[email protected]||1003 Qinhuangdao Street||3552532019|
|Mary||Smith||[email protected]||1913 Hanoi Way||2845819439|
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
IF EXISTS, in the following query, is optional.
DROP VIEW IF EXISTS customer_master_list;