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;
Comments
Post a Comment