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 BYand joins. CREATE OR REPLACE VIEWchanges the query of a view that already exists.
In the exercise, create a view of your own.