Article Overview

This article will help you to understand what is a database view in general and in turn what is a PostgreSQL view. This article will help you to learn how to create/query/drop a PostgreSQL view, their syntaxes with working examples, and how to work with vies via the Psql terminal or PgAdmin tool.

What is a view?

A view in SQL is considered a virtual table that is based on the result-set of an SQL statement. Similar to a real table, a view consists of rows and columns and it can have data from one or more tables. A view does not contain any data nor does not need to be physically stored, i.e. it is not stored in memory and does not take up actual storage space. Similar to a SQL table, the view name should be unique in a database. As views are assigned separate permissions, they can be used to restrict table access so that the users see only specific rows or columns of a table.

A PostgreSQL view satisfies all the above mentioned characteristics and can be created using the below syntax:

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

In this syntax, the 'view_name' is specified after the 'CREATE VIEW' keywords, followed by the required column(s) from the table followed by the where condition. The 'Temp/Temporary' keyword is optional and if specified creates a temporary view. The temporary view ceases to exist at the end of the current session.

Example:

CREATE VIEW STOCK_VIEW AS
SELECT stock_id, stock_name, stock_price
FROM stocks;

To List View(s) in Psql terminal:

To list all view(s) within a PostgreSQL schema, '\dv'  command can be used.

CREATE VIEW in PgAdmin

To create a view in PgAdmin, one has to navigate to the 'views->Create->View' section from the menu as shown below:

List View(s) in PgAdmin

Once a view is created either through Psql terminal or via PgAdmin, it is listed under 'Views' as shown below and is available for use.

Query View

The query to select data from a PostgreSQL view is similar to that of querying data from a PostgreSQL table. Syntax is given below:

Select * from stock_view;

Query View in PgAdmin

To query a view in PgAdmin, one needs to traverse through the menu to 'Views->View->View/Edit Data'. Next, user is greeted with few options from which user can make choice based on his requirement and proceed to fetch the data. Note that though the option here reads 'View/Edit Data', views cannot be edited. However, if the original table data is updated. it is reflected in the view as well.

Drop View

The query to drop a view is short and simple. The view to be dropped or the 'view_name' is specified right after the 'DROP VIEW' keyword.

DROP VIEW view_name;

Drop View in PgAdmin

To drop a view in PgAdmin, one needs to traverse through the menu to 'Views->View->Delete/Drop'

Conclusion

This article gives an overview on the different aspects of a PostgreSQL view and how can they be used in the practical world. We sincerely hope this article would help you get started on your PostgreSQL View journey.