PostgreSQL Views

A View is a virtual table, essentially a PostgreSQL statement stored in the database under an associated name.

A View is actually a combination of tables in the form of a predefined PostgreSQL query.

A View can contain all rows of a table or selected rows from one or more tables.

A View can be created from one or more tables, depending on the PostgreSQL query used to create the view.

A View is a virtual table that allows users to achieve the following:

• A way for users or user groups to find structured data in a more natural or intuitive manner.
• Restrict data access, so users can only see limited data rather than the complete table.
• Summarize data from various tables for generating reports.

PostgreSQL views are read-only, so DELETE, INSERT, or UPDATE statements may not be executed on a view. However, you can create a trigger on a view that fires when a DELETE, INSERT, or UPDATE on the view is attempted, and the actions to be taken are defined in the trigger's content.

CREATE VIEW (Create View)

In PostgreSQL, use the CREATE VIEW statement to create a view. A view can be created from one table, multiple tables, or other views.

The basic syntax of CREATE VIEW is as follows:

CREATE [TEMP | TEMPORARY] VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE [condition];

You can include multiple tables in the SELECT statement, much like in a normal SQL SELECT query. If the optional TEMP or TEMPORARY keyword is used, the view will be created in a temporary database.

Example

Create the COMPANY table (Download the COMPANY SQL file), the data content is as follows:

exampledb# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

Now, the following is an example of creating a view from the COMPANY table. The view selects only a few columns from the COMPANY table:

exampledb=# CREATE VIEW COMPANY_VIEW AS
SELECT ID, NAME, AGE
FROM  COMPANY;

Now, you can query COMPANY_VIEW in a manner similar to querying an actual table. Here is an example:

exampledb# SELECT * FROM COMPANY_VIEW;

The result obtained is as follows:

id | name  | age
----+-------+-----
  1 | Paul  |  32
  2 | Allen |  25
  3 | Teddy |  23
  4 | Mark  |  25
  5 | David |  27
  6 | Kim   |  22
  7 | James |  24
(7 rows)

DROP VIEW (Drop View)

To delete a view, simply use the DROP VIEW statement with view_name. The basic syntax of DROP VIEW is as follows:

exampledb=# DROP VIEW view_name;

The following command will delete the COMPANY_VIEW view we created earlier:

exampledb=# DROP VIEW COMPANY_VIEW;