Views in PostgreSQL Explained

The view is a virtual table formed based on the defined SQL query. It can also be defined as a stored query. It owns rows and columns just like a real table. It contains only those rows and columns which are specified in the SELECT statement of the query. It can also combine data from more than one table using JOIN, UNION, INTERSECTION, and sub-queries.

A simple instruction to create a view

CREATE VIEW view_name AS query;

The above instruction will create a view in the current schema. To create a view in the specific schema, it should be referenced with that schema_name like

CREATE VIEW schema_name.view_name AS query;

While creating a view, make sure its name must be distinct from other indexes, sequences, tables, views, and foreign tables in the same schema.

A view can be temporary or permanent. To create a temporary view, the same instruction will be used with the TEMPORARY or TEMP keyword.

CREATE VIEW [ TEMPORARY | TEMP ] view_name AS query;

The temporary view will be dropped automatically at the end of the current session. If a view referenced any temporary table then it is created as a temporary view.

Let's take a database with two tables as student and class. student table contains the foreign key of class table. A join query for student and class table will be used to get all the students from a specific class. To create a view for that query

CREATE VIEW student_class AS SELECT
std.first_name,
std.last_name,
cls.class_name
FROM student as std JOIN class as cls
ON std.class_id = cls.id
WHERE cls.id = 2;

To access the data from a view, use a SELECT statement like accessing data from real table.

SELECT * FROM student_class;

To replace views use REPLACE in the instruction,

CREATE OR REPLACE VIEW view_name AS query;

The replacing view’s new columns must have the same name, data types, and order.

To enlist all the views in a database use the following statement.

SELECT viewname FROM pg_catalog.pg_views;

To change the name of the view use the ALTER VIEW instruction with RENAME like below.

ALTER VIEW old_view_name RENAME TO new_view_name;

To remove a view from the database use the DROP VIEW statement,

DROP VIEW IF EXISTS view_name [ CASCADE | RESTRICT ];

It will drop the view if it exists. CASCADE and RESTRICT are optional parameters. If all the dependence needs to be removed then use CASCADE else use RESTRICT. To drop a view, a user should be the owner of the view.

A view can be update-able and translate the query to the underlying table. To update the view it should meet the following conditions.

1- It must own a single entity in the FORM clause.
2- It must not contain the following clauses, GROUP BY, HAVING LIMIT, OFFSET, DISTINCT, WITH, UNION, INTERSECTION, and EXCEPT
3- The selection list must not contain any function.

An updatable view can own both updateable and non-updateable columns. INSERT, UPDATE and DELETE queries works same for views as it works with tables. Let's create a student view.

CREATE VIEW student AS SELECT first_name, last_name FROM Student;

To insert into student view use the following statement.

INSERT INTO student (first_name, last_name) VALUES ('John', 'David');

A view can update even those rows which do not meet the defined conditions inside the view. To update only those rows which satisfy the defined conditions inside the view, use WITH CHECK OPTION.

CREATE VIEW student AS SELECT first_name, last_name FROM Student WITH CHECK OPTION;

And that’s it. I hope you have found this introduction to views in ProgreSQL useful.

More content at plainenglish.io