PostgreSQL Views | PostgreSQL Tutorials and Solutions

What is view?

In this article, we will see what is view, why views? and examples of PostgreSQL views.

PostgreSQL View can be seen as a named query, or as a wrapper around a SELECT statement.

Why PostgreSQL views?

A view can be seen as a named query, or as a wrapper around a SELECT statement.
Tuning performance by caching the view results for later use.
Decreasing the amount of SQL code
Bridging the gap between relational databases and OO languages—especially updatable views
Implementing authorization at the row level by leaving out rows that do not meet a certain predicate
Implementing interfaces and the abstraction layer between high level languages and relational databases
Implementing last minute changes without redeploying the software

PostgreSQL Views Syntax:

CREATE VIEW is used to create views in PostgreSQL Server. DROP VIEW is used to drop a view from PostgreSQL Server.

CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] [ RECURSIVE ] VIEW name [ ( column_name [, ...] ) ]
    [ WITH ( view_option_name [= view_option_value] [, ... ] ) ]
    AS query
    [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]

PostgreSQL View Examples:

1. Create a view on emp table.

CREATE VIEW emp_clerk AS SELECT * FROM emp where job='CLERK';

2. To list of views use \dv

3. To check particular view use \dv view_name.

4. Update PostgreSQL view use the below syntax.

ALTER VIEW [schema_name.]view_name AS
  [ WITH { ENCRYPTION | SCHEMABINDING | VIEW_METADATA }
  SELECT expressions
  FROM tables
  WHERE conditions;

5. To drop a view use drop view command.

drop view emp_clerk;