PostgreSQL View (View)

View (View) is a pseudo-table, which is just a PostgreSQL statement stored in the database with a related name.

View (View) is actually a combination of tables that exist in the form of a predefined PostgreSQL query.

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

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

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

• A way for users or user groups to find structured data more naturally or intuitively.

• Limit data access so that users can only see limited data rather than the entire table.

• Summarize data from various tables for report generation.

PostgreSQL views are read-only, so it may not be possible to execute DELETE, INSERT, or UPDATE statements on the view. However, a trigger can be created on the view, which will be triggered when trying to DELETE, INSERT, or UPDATE the view, and the actions to be taken are defined in the trigger content.

CREATE VIEW (Create View)

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

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

Online Example

Create COMPANY table (Download COMPANY SQL file ），data content is as follows：

w3codeboxdb# 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 only selects a few columns from the COMPANY table:

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

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

w3codeboxdb# 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 (Delete View)

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

w3codeboxdb=# DROP VIEW view_name;

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

w3codeboxdb=# DROP VIEW COMPANY_VIEW;