Home > Articles > Data

Working with Views in PostgreSQL

  • Print
  • + Share This
Views are a core component of relational and object relational databases, and many operations can be performed only by defining a set of views. In this article, derived from the authors' forthcoming book, PostgreSQL Developer's Handbook (Sams, 2001, ISBN: 0672322609), you learn how views can be used efficiently and how data in a view can be modified.
This chapter is from the book

This chapter is from the book

Simple Examples of Views

It has been said that eyes are the windows to the soul. No matter whether this statement is true or not, there are many ways to look at data and to view the content of tables.

Views monitor the result of an SQL statement in a table. This does not mean that the data in a table is copied and physically written to disk. Views are just similar to eyes, and these eyes provide a view on the data.

Let's have a look at a simple example:

CREATE TABLE data(a int4, b int4);
INSERT INTO data VALUES(12, 23);
CREATE VIEW view_data AS SELECT a*2 AS c, b*2 AS d FROM data;

We have created a table called "data", and have inserted one record into it. Then we create a view called "view_data" containing two columns. Let's have a look at the definition of the view:

view=# \d view_data
    View "view_data"
 Attribute | Type  | Modifier
 c     | integer |
 d     | integer |
View definition: SELECT (data.a * 2) AS c, (data.b * 2) AS d FROM data;

When querying the table, we can see what is returned by the database.

view=# SELECT * FROM view_data;
 c | d
 24 | 46
(1 row)

The view contains the result of the SQL statement. The advantage of a view like that is that it provides the opportunity to build complex applications where many steps and operations are involved.

One important fact when dealing with views is to see what the database has to do internally in order to execute the query:

view=# EXPLAIN SELECT * FROM view_data;
Seq Scan on data (cost=0.00..20.00 rows=1000 width=8)

You can see that PostgreSQL performs a sequential scan on "data". This shows clearly that a view is only a virtual table and not a real table like "data". Therefore, it is not possible to perform INSERT and UPDATE operations without telling the database how these operations have to be done:

view=# INSERT INTO view_data VALUES(34, 68);
ERROR: Cannot insert into a view without an appropriate rule
  • + Share This
  • 🔖 Save To Your Account