PostgreSQL Views

Introduction

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:

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:

Code:

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:

Code:

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:

Code:

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:

Code:

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:

Code:

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:

Code:

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:

Code:

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:

Code:

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:

Code:

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:

Code:

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:

Example:

To rename the view abc to xyz

Code:

ALTER VIEW my_view RENAME TO myview;

To attach a default column value to an updatable view:

Code:

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:

Example:

This command will remove the view called 'test_view':

DROP VIEW test_view;

Reference: PostgreSQL Manual

Previous: SUBQUERIES
Next: TRIGGERS