❮ Postgresql Lock Postgresql Privileges ❯

PostgreSQL View

A View is a virtual table, which is essentially a stored PostgreSQL statement with a related name in the database.

A View is actually a combination of tables existing 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 multiple tables, depending on the PostgreSQL query for creating the view.

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

A more natural or intuitive way for users or user groups to look up structured data.
Restricting data access, where users can only see limited data instead of the complete table.
Summarizing data from various tables for report generation.

PostgreSQL views are read-only, so DELETE, INSERT, or UPDATE statements may not be executed on a view. However, a trigger can be created on the view that fires when attempting to DELETE, INSERT, or UPDATE the view, with the required actions defined within the trigger content.

CREATE VIEW

In PostgreSQL, a view is created using the CREATE VIEW statement, which can be created from one table, multiple tables, or other views.

The basic syntax for 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, similar to how it is done in a regular SQL SELECT query. If the optional TEMP or TEMPORARY keyword is used, the view will be created in the temporary database.

Example

Create a COMPANY table (download the COMPANY SQL file from here), with the following data content:

tutorialprodb# 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, here is an example of creating a view from the COMPANY table. The view selects only a few columns from the COMPANY table:

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

Now, you can query the COMPANY_VIEW, similar to how you query an actual table. Here is an example:

tutorialprodb# SELECT * FROM COMPANY_VIEW;

The result 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

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

tutorialprodb=# DROP VIEW view_name;

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

tutorialprodb=# DROP VIEW COMPANY_VIEW;

❮ Postgresql Lock Postgresql Privileges ❯