One of PostgreSQL’s more useful
capabilities is the ability to create custom “views”. These views are
nothing more than pre-defined SQL queries, which are stored in the database and
can be re-used as and when needed. Storing frequently-used SQL queries in this
manner is more efficient than typing them out each time and also offers greater
flexibility, because the data set generated by a view can itself be manipulated
using SQL.

This document will show you how to create, use and delete
views in PostgreSQL.

More easily
copy and paste versions of Listings A-E are included in the text file included
in the downloadable Zip file.

Sample tables

Begin by creating three sample tables, using the SQL
commands below:

test=# CREATE TABLE stories (id INT, title VARCHAR, time TIMESTAMP);

test=# CREATE TABLE authors (id INT, name VARCHAR);

test=# CREATE TABLE stories_authors_link (story INT, author INT);

The commands above create three tables: one for story
titles, one for author names, and one mapping stories
to authors. Populate these tables with some sample records. (Listing A)

Listing A

test=# INSERT INTO authors VALUES (1, ‘John Doe’);
test=# INSERT INTO authors VALUES (2, ‘James White’);
test=# INSERT INTO authors VALUES (3, ‘Ellen Sue’);
test=# INSERT INTO authors VALUES (4, ‘Gina Haggelstrom’);
test=# INSERT INTO authors VALUES (5, ‘Jane Ki’);

test=# INSERT INTO stories VALUES (100, ‘All Tied Up’, ‘2005-04-01 12:37:00’);
test=# INSERT INTO stories VALUES (112, ‘Into Thin Air…’, ‘2005-04-02 06:54:12’);
test=# INSERT INTO stories VALUES (127, ‘The Oxford Blues’, ‘2005-06-12 18:01:43’);
test=# INSERT INTO stories VALUES (128, ‘Crash!’, ‘2005-03-27 09:12:17’);
test=# INSERT INTO stories VALUES (276, ‘Memories Of Malgudi’, ‘2005-06-09 23:35:57’);
test=# INSERT INTO stories VALUES (289, ‘The Big Surprise’, ‘2005-05-30 08:21:02’);
test=# INSERT INTO stories VALUES (301, ‘Indians and The Cowboy’, ‘2005-04-16 11:19:28’);

test=# INSERT INTO stories_authors_link VALUES (112, 2);
test=# INSERT INTO stories_authors_link VALUES (127, 1);
test=# INSERT INTO stories_authors_link VALUES (128, 5);
test=# INSERT INTO stories_authors_link VALUES (276, 5);
test=# INSERT INTO stories_authors_link VALUES (289, 3);
test=# INSERT INTO stories_authors_link VALUES (301, 5);
test=# INSERT INTO stories_authors_link VALUES (100, 1);

Next, let’s suppose we want to obtain a comprehensive report
of available stories and their authors. This is best accomplished by joining
the three tables on their common fields, as shown in Listing B.

Listing B

test=# SELECT s.title, a.name, s.time
test-# FROM stories AS s, authors AS a, stories_authors_link AS sa
test-# WHERE s.id = sa.story
test-# AND a.id = sa.author
test-# ORDER BY s.time
test-# DESC;
title                   |    name     |        time
————————+————-+———————
 The Oxford Blues       | John Doe    | 2005-06-12 18:01:43
 Memories Of Malgudi    | Jane Ki     | 2005-06-09 23:35:57
 The Big Surprise       | Ellen Sue   | 2005-05-30 08:21:02
 Indians and The Cowboy | Jane Ki     | 2005-04-16 11:19:28
 Into Thin Air…       | James White | 2005-04-02 06:54:12
 All Tied Up            | John Doe    | 2005-04-01 12:37:00
 Crash!                 | Jane Ki     | 2005-03-27 09:12:17
(7 rows)

Naturally, it’s inefficient to type out such a long query
over and over again. Therefore, it makes sense to save this query as a view.
Here’s how (Listing C).

Listing C

test=# CREATE VIEW myview AS SELECT s.title, a.name, s.time FROM stories AS s, authors AS a, stories_authors_link AS sa WHERE s.id = sa.story AND a.id = sa.author ORDER BY s.time DESC;

The syntax to create a view is CREATE VIEW
name AS query. This will save the query string query to the database under the name name. You can
verify this by checking the output of the \dvcommand, as
below:

test=# \dv
       List of relations
 Schema |  Name  | Type | Owner
——–+——–+——+——-
 public | myview | view | pgsql
(1 row)

To re-use a view, run a SELECT query on it, as though it were a
normal table. Listing D shows you
how.

Listing D

test=# SELECT * FROM myview;
         title          |    name     |        time
————————+————-+———————
 The Oxford Blues       | John Doe    | 2005-06-12 18:01:43
 Memories Of Malgudi    | Jane Ki     | 2005-06-09 23:35:57
 The Big Surprise       | Ellen Sue   | 2005-05-30 08:21:02
 Indians and The Cowboy | Jane Ki     | 2005-04-16 11:19:28
 Into Thin Air…       | James White | 2005-04-02 06:54:12
 All Tied Up            | John Doe    | 2005-04-01 12:37:00
 Crash!                 | Jane Ki     | 2005-03-27 09:12:17
(7 rows)

As Listing D illustrates,
SELECT-ing from a view actually executes the original saved query.
Naturally, you can further manipulate the output of a view by using SQL
modifiers with the SELECT statement.
For example, Listing E returns the
top three stories only.

Listing E

test=# SELECT title, name FROM myview LIMIT 3;
        title        |   name
———————+———–
 The Oxford Blues    | John Doe
 Memories Of Malgudi | Jane Ki
 The Big Surprise    | Ellen Sue
(3 rows)

You can delete a view with the DROP VIEW name command, where name is the name of the view to be erased.
Here’s an example:

test=# DROP VIEW myview;

Verify that the view no longer exists by checking the output
of the \dv command:

test=# \dv
No relations found.

As the above examples illustrate, views provide a convenient
shortcut to oft-used SELECT queries, and
also makes it easy to obtain different perspectives of the same data. Try them
out yourself.