YouTipPostgresql View
## PostgreSQL Views: A Comprehensive Guide
In PostgreSQL, a **View** is a virtual table representing the result of a pre-defined SQL query. It does not store physical data itself. Instead, it acts as a dynamic window through which you can query, aggregate, or filter data stored in one or more underlying physical tables (often called base tables).
### Why Use Views?
Views are highly powerful database objects that allow developers and database administrators to:
* **Simplify Complex Queries:** Wrap complex joins, aggregations, and subqueries into a single virtual table. Users can query the view using simple `SELECT` statements.
* **Enhance Security and Restrict Access:** Implement column-level or row-level security. You can grant users access to a view containing only a subset of columns or rows, while restricting access to the underlying base tables.
* **Provide Data Consistency:** Present a clean, logical data structure to application developers, even if the underlying database schema changes or is highly normalized.
* **Generate Reports:** Consolidate data from multiple tables to create standardized reporting structures.
---
## CREATE VIEW
You can create a view in PostgreSQL using the `CREATE VIEW` statement. Views can be built from a single table, multiple tables (via joins), or even other views.
### Syntax
The basic syntax for creating a view is as follows:
```sql
CREATE [TEMP | TEMPORARY] VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
```
* **`TEMP` or `TEMPORARY`**: Optional keywords. If specified, the view is created as a temporary view. Temporary views are automatically dropped at the end of the current database session.
* **`view_name`**: The name of the view you want to create.
* **`AS`**: Specifies the query that defines the view.
---
## Step-by-Step Example
Let's walk through a practical example of creating, querying, and managing a view.
### 1. The Base Table
Suppose we have a table named `COMPANY` containing employee details:
```sql
runoobdb=# SELECT * FROM COMPANY;
id | name | age | address | salary
----+-------+-----+------------+--------
1 | Paul | 32 | California | 20000
2 | Allen | 25 | Texas | 15000
3 | Teddy | 23 | Norway | 20000
4 | Mark | 25 | Rich-Mond | 65000
5 | David | 27 | Texas | 85000
6 | Kim | 22 | South-Hall | 45000
7 | James | 24 | Houston | 10000
(7 rows)
```
### 2. Creating a View
If we want to expose only the basic public information of employees (`id`, `name`, and `age`) while hiding sensitive details like `address` and `salary`, we can create a view named `COMPANY_VIEW`:
```sql
runoobdb=# CREATE VIEW COMPANY_VIEW AS
SELECT id, name, age
FROM COMPANY;
```
### 3. Querying the View
Once the view is created, you can query it exactly like a standard physical table:
```sql
runoobdb=# SELECT * FROM COMPANY_VIEW;
```
**Output:**
```sql
id | name | age
----+-------+-----
1 | Paul | 32
2 | Allen | 25
3 | Teddy | 23
4 | Mark | 25
5 | David | 27
6 | Kim | 22
7 | James | 24
(7 rows)
```
---
## DROP VIEW
If a view is no longer needed, you can remove it from the database schema using the `DROP VIEW` statement.
### Syntax
```sql
DROP VIEW view_name [CASCADE | RESTRICT];
```
* **`IF EXISTS`**: Prevents an error from occurring if the view does not exist.
* **`CASCADE`**: Automatically drops objects that depend on the view (such as other views).
* **`RESTRICT`**: Refuses to drop the view if any objects depend on it (this is the default behavior).
### Example
To delete the `COMPANY_VIEW` we created earlier:
```sql
runoobdb=# DROP VIEW COMPANY_VIEW;
```
---
## Important Considerations
### Read-Only vs. Updatable Views
By default, simple views in PostgreSQL are automatically updatable. This means you can run `INSERT`, `UPDATE`, or `DELETE` statements on the view, and PostgreSQL will translate those operations to the underlying base table.
However, a view is **not** automatically updatable if it contains any of the following:
* `WITH`, `DISTINCT`, `GROUP BY`, `HAVING`, `LIMIT`, or `OFFSET` clauses.
* Set operations like `UNION`, `INTERSECT`, or `EXCEPT`.
* Aggregations or window functions.
* Joins (unless specific conditions are met).
### Modifying Complex Views with Triggers
If you need to perform write operations (`INSERT`, `UPDATE`, `DELETE`) on a complex, read-only view, you can define an **`INSTEAD OF` trigger**. This trigger intercepts the write operation on the view and executes custom SQL logic on the underlying base tables instead.