Working with Views in PostgreSQL
- Simple Examples of Views
- Modifying the Content of Views
- Conclusion
This chapter is from the book
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; NOTICE: QUERY PLAN: Seq Scan on data (cost=0.00..20.00 rows=1000 width=8) EXPLAIN
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