Postgres – What are Views in PostgreSQL ?

Views are basically a query with a name; therefore, a view is useful for wrapping a commonly used complex query; we can represent data in the database tables using views named query, we can define views using one or more tables known as base tables, the view is a logical table which represents data from one or many tables.using the SELECT command.

The query’s complexity can be simplified with the help of view as users can query a view using a SELECT command. Like a table, we can add permission to the users using a view that stores data for which we need authorized users only.

How to Create PostgreSQL Views?
Let us see how to create the views:

Syntax:

CREATE [OR REPLACE][TEMP OR TEMPORARY] [RECURSIVE] VIEW view_name [(column_name [, …])] [ WITH (view_options_name [= view_options_value] [, … ])]

Explanation: If a view with the same name already exists, it is replaced. The view name must be unique. It should not be the same as any other view, sequence, table, foreign table or index in the same schema.

TEMP / TEMPORARY: If the view is created as a temporary view, then it is automatically removed at the end of the session.
RECURSIVE: Creates a recursive view.
name: The name of a view to be
column_name: The user can define a list of column names of the view. Only defined columns will get considered in query others are
WITH: ( view_options_name [= view_options_value]…)We can specify optional parameters for a view.
Creating PostgreSQL Views
We can create PostgreSQL views using various ways:

Consider the following tables to understand the PostgreSQL views:

To understand the examples of considering the following ‘student’ and ‘Branch’ table structures

Student: rollno, firstname, lastname, branch_id, result, joining_date
Branch: branch_id, branch
1. WHERE clause
Code:

CREATE VIEW student_view
AS SELECT rollno, firstname, lastname, result, joining_date
FROM student
WHERE branch_id = 4;

It will create a view ‘student _view’ taking records (for rollno, firstname, lastname, result, joining_date columns) of the student table if those records contain the value 4 for branch_id column.

Code:

select * from student_view;

2. AND and OR
Code:

CREATE VIEW my_student_view
AS SELECT *
FROM student
WHERE(branch_id = 1 AND result = false)
OR(branch_id = 2 AND firstname=’Jacob’);

It will create a view ‘my_student_view’ taking records for all student table columns
if branch_id is 1, and the result is false.
or branch_id is 2, and the firstname is ‘Jacob’.

3. GROUP BY
Code:

CREATE VIEW my_student_view
AS SELECT branch_id, count (*)
FROM student
GROUP BY branch_id;

It will create a view ‘my_student_view’ taking all records grouped with respect to branch_id and stored branch_id and several students for each Branch (branch_id) from the student table

4. ORDER BY
Code:

CREATE VIEW my_student_view
AS SELECT branch_id, count (*)
FROM student
GROUP BY branch_id
order by branch_id;

It will create a view of my_student_view, taking all the records grouped with respect to branch_id and sorted against branch_id and the number of student tables for each department (branch_id) from the student table.

#ViewsinPostgreSQL, #ViewsinSQL, #Views