How to Create PostgreSQL View

A view is a data object which does not contain any data. Contents of the view are the resultant of a base table. They are operated just like a base table but they don’t contain any data of their own. The difference between a view and a table is that views are definitions built on top of other tables (or views). If data is changed in the underlying table, the same change is reflected in the view. A view can be built on top of a single or multiple tables.

Version : PostgreSQL 9.3.5

How to Create PostgreSQL View?

CREATE VIEW defines a view of a query. The view is not physically materialized. Instead, the query is run every time the view is referenced in a query. By default, a view is associated with the default database (currently used database). To associate the view with a given database, specify the name as database_name. view_name when you create it. Here is the complete syntax :

Syntax

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

CREATE OR REPLACE VIEW is similar, but if a view of the same name already exists, it is replaced. If a schema name is given (for example, CREATE VIEW hr.myview ...) then the view is created in the specified schema. Otherwise, it is created in the current schema. Temporary views exist in a special schema, so a schema name cannot be given when creating a temporary view. The name of the view must be distinct from the name of any other view, table, sequence, index or foreign table in the same schema.

Parameters :

Operator	Description
TEMPORARY or TEMP	If specified, the view is created as a temporary view. Temporary views are automatically dropped at the end of the current session.
RECURSIVE	Creates a recursive view.
name	The name of a view to been created.
column_name	An optional list of names to be used for columns of the view. If not given, the column names are deduced from the query.
WITH ( view_option_name [= view_option_value] [, ... ] )	This clause specifies optional parameters for a view; currently, the only supported parameter name is security_barrier, which should be enabled when a view is intended to provide row-level security.
query	A SELECT or VALUES command which will provide the columns and rows of the view.

Note :

The syntax of the recursive view is :

CREATE RECURSIVE VIEW name (columns) AS SELECT ...;

is equivalent to

CREATE VIEW name AS WITH RECURSIVE name (columns) AS (SELECT ...) SELECT columns FROM name;

Examples

Here is an example using RECURSIVE :

CREATE VIEW my_view AS  
    WITH RECURSIVE my_view(n) AS (  
       SELECT 1  
     UNION ALL  
       SELECT n+1 FROM my_view WHERE n <=5  
   )  
   SELECT * FROM my_view;  

Sample table : employees

Sample table : locations

Sample table : departments

PostgreSQL CREATE VIEW with WHERE

CREATE VIEW command can be used with WHERE clause.

Example :

CREATE VIEW emp_view  
AS SELECT employee_id, first_name,last_name, hire_date  
FROM employees   
WHERE department_id = 200;  

The above PostgreSQL statement will create a view 'emp_view' taking records (for employee_id, first_name, last_name and hire_date columns) of employees table if those records contain the value 200 for department_id column.

PostgreSQL CREATE VIEW with AND and OR

CREATE VIEW command can be used with AND and OR operators.

Example :

CREATE VIEW my_view  
AS SELECT *  
FROM locations  
WHERE (country_id='US' AND city='Seattle')  
OR  (country_id=JP' AND city='Tokyo'); 

The above PostgreSQL statement will create a view 'my_view' taking records for all columns of locations table, if (A)(i)value of the country_id column is US, and (ii)value of the city is Seattle; or (B)(i)value of the country_id column is JP, and (ii)value of the city is Tokyo.

PostgreSQL CREATE VIEW with GROUP BY

CREATE VIEW command can be used with GROUP BY clause.

Example :

CREATE VIEW my_view  
AS SELECT department_id, count(*)  
FROM employees  
GROUP BY department_id; 

The above statement will create a view 'my_view' taking all records grouped w.r.t. department_id, and stored department_id and a number of employees for each department (department_id) from employees table.

PostgreSQL CREATE VIEW with ORDER BY

CREATE VIEW command can be used with ORDER BY clause.

Example :

CREATE VIEW my_view  
AS SELECT department_id,count(*)   
FROM employees   
GROUP BY department_id  
ORDER BY department_id;  

The above PostgreSQL statement will create a view 'my_view' taking all the records grouped w.r.t. department_id and sorted against department_id and number of employees for each department (department_id) from employees table.

PostgreSQL CREATE VIEW with BETWEEN and IN

CREATE VIEW command can be used with BETWEEN and IN operator.

Example :

CREATE VIEW my_view  
AS SELECT *  
FROM employees  
WHERE first_name BETWEEN 'A' AND 'H'   
AND salary IN(4000,7000,9000,10000,12000);  

The above statement will create a view 'my_view' taking all the records of employees table, if (A)first_name of the employee starts with any of the characters from 'A' through 'H' and (B) salaries are any of the following 4000,7000,9000,10000,12000.

PostgreSQL CREATE VIEW with LIKE

CREATE VIEW command can be used with LIKE operator.

Example :

CREATE VIEW my_view  
AS SELECT *  
FROM employees  
WHERE first_name  
NOT LIKE 'T%' AND last_name NOT LIKE 'T%';  

The above PostgreSQL statement will create a view 'my_view' taking all the records of employees table, if (A)first_name of the employee does not start with 'T' and (B) last_name of the employee does not start with 'T'.

PostgreSQL CREATE VIEW using subqueries

CREATE VIEW command can be used with subqueries.

Example :

CREATE VIEW my_view  
AS SELECT employee_id,first_name,last_name   
FROM employees  
WHERE department_id IN(  
SELECT department_id   
FROM departments   
WHERE location_id IN (1500,1600,1700)  
);  

The above PostgreSQL statement will create a view 'my_view' taking all the records of employee_id, first_name, last_name of employees table, if department_id satisfies the condition defined within a subquery (followed by department_id IN ).

The subquery retrieves those department_id's from departments table, which location_id are any of the list 1500,1600,1700.

PostgreSQL CREATE VIEW with JOIN

CREATE VIEW command can be used along with a JOIN statement.

Example :

CREATE VIEW my_view  
AS SELECT a.employee_id,a.first_name,a.last_name, b.department_name,  
b.location_id          
FROM employees a,departments b   
WHERE a.department_id=b.department_id; 

The above PostgreSQL statement will create a view 'my_view' along with a JOIN statement.

The JOIN statement here retrieves employee_id, first_name, last_name, from employees table and department_id and location_id from locations table if department_id of employees table and that of locations are same.

PostgreSQL CREATE VIEW with UNION

CREATE VIEW command can be used with UNION.

Example :

CREATE VIEW my_view AS  
SELECT *   
FROM employees  
WHERE manager_id=100   
UNION  
SELECT *   
FROM employees  
WHERE first_name BETWEEN 'P' AND 'W'   
UNION  
SELECT *  
FROM employees  
WHERE salary IN(7000,9000,10000,12000); 

The above PostgreSQL statement will create a view 'my_view' contains columns as in the 'employees'.

The records will be inserted with the union of three subqueries.

The first query inserts those rows into the 'my_view' view from the 'employees' table whose 'manager_id' is '100'.

The second query inserts those rows into the 'my_view' view from the 'employees' table whose rows have the 'first_name' column beginning with any letter between 'P' to 'W'.

The third query inserts those rows into the 'my_view' view from the 'employees' table whose rows have any of the following values 7000,9000,10000,12000 in 'salary'.

Alter a view

ALTER VIEW statement changes the definition of an existing view. This statement requires the CREATE VIEW and DROP privileges for the view, and some privilege for each column referred to in the SELECT statement. The syntax of the statement is similar to CREATE VIEW. Here is the syntax :

ALTER VIEW [ IF EXISTS ] name ALTER [ COLUMN ] column_name SET DEFAULT expression
ALTER VIEW [ IF EXISTS ] name ALTER [ COLUMN ] column_name DROP DEFAULT
ALTER VIEW [ IF EXISTS ] name OWNER TO new_owner
ALTER VIEW [ IF EXISTS ] name RENAME TO new_name
ALTER VIEW [ IF EXISTS ] name SET SCHEMA new_schema
ALTER VIEW [ IF EXISTS ] name SET ( view_option_name [= view_option_value] [, ... ] )
ALTER VIEW [ IF EXISTS ] name RESET ( view_option_name [, ... ] )

Parameters :

Operator	Description
name	The name of an existing view.
IF EXISTS	Do not throw an error if the view does not exist. A notice is issued in this case.
SET/DROP DEFAULT	These forms set or remove the default value for a column.
new_owner	The user name of the new owner of the view.
new_name	The new name for the view.
new_schema	The new schema for the view.
view_option_name	The name of a view option to be set or reset.
view_option_value	The new value for a view option.

To rename the view abc to xyzExample :

ALTER VIEW my_view RENAME TO myview;  

To attach a default column value to an updatable view :

CREATE TABLE testtable (id int, tz timestamptz);  
CREATE VIEW test_view AS SELECT * FROM testtable  
ALTER VIEW test_view ALTER COLUMN tz SET DEFAULT now(); 

Drop a view

DROP VIEW statement is used to remove a views. To drop a view, you must have DROP privilege for each view. Here is the syntax :

Syntax

DROP VIEW [IF EXISTS]      
     view_name [, view_name] ...      
  [ CASCADE | RESTRICT]

Parameters :

Operator	Description
IF EXISTS	Do not throw an error if the view does not exist. A notice is issued in this case.
name	The name (optionally schema-qualified) of the view to remove.
CASCADE	Automatically drop objects that depend on the view (such as other views).
RESTRICT	Refuse to drop the view if any objects depend on it. This is the default.

Example :

This command will remove the view called 'test_view' :

DROP VIEW test_view;