VIEWS

A view is a saved query that you can use like a table. It does not store its own data. Every time you read the view, PostgreSQL runs its query on the real tables.

Views are useful to save a query you use often, to make a long query easier to use, or to show only some columns of a table.


Syntax

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

To read from a view:

SELECT * FROM view_name;

To remove a view:

DROP VIEW view_name;

Example: A View for IT Employees

CREATE VIEW it_employees AS
SELECT id, name, age
FROM employees
WHERE department = 'IT';

SELECT * FROM it_employees;

Output:

CREATE VIEW
 id |  name  | age 
----+--------+-----
  2 | Bob    |  32
  5 | Emma   |  26
  9 | Mike   |  24
 11 | Sarah  |  28
 15 | Olivia |  25
 20 | Thomas |  33
(6 rows)

The view returns only the IT employees, with three columns.


A View Always Shows the Latest Data

Because a view runs its query each time, it sees every change to the table. Let's move Lucy to IT and read the view again:

UPDATE employees SET department = 'IT'
WHERE name = 'Lucy';

SELECT * FROM it_employees ORDER BY id;

Output:

UPDATE 1
 id |  name  | age 
----+--------+-----
  2 | Bob    |  32
  5 | Emma   |  26
  9 | Mike   |  24
 11 | Sarah  |  28
 13 | Lucy   |  30
 15 | Olivia |  25
 20 | Thomas |  33
(7 rows)

Lucy is in the view now, even though the view was created before she moved.


Key Points

  • A view does not store data. It shows data from the tables it reads.
  • You can use a view in most places where you use a table, like WHERE, ORDER BY and joins.
  • CREATE OR REPLACE VIEW changes the query of a view that already exists.

In the exercise, create a view of your own.