PostgreSQL CREATE VIEW
Views in PostgreSQL are virtual tables that represent the data from one or more tables through a SQL query. They don't store data themselves but provide a way to look at data in the database.
What is a PostgreSQL View?
A view is essentially a saved SQL query that you can reference like a table. When you query a view, PostgreSQL executes the underlying query and returns the results. Views allow you to:
- Simplify complex queries
- Restrict access to specific data
- Present data in a customized format
- Encapsulate business logic
- Improve database security
Basic Syntax
The basic syntax for creating a view in PostgreSQL is:
CREATE [OR REPLACE] VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
Let's break down the components:
CREATE VIEW: Initiates the view creationOR REPLACE: Optional clause that allows you to update an existing viewview_name: The name you want to give to your viewAS: Connects the view name with its defining querySELECT...: The query that defines what data the view will present
Creating a Simple View
Let's look at a basic example of creating a view:
-- Create a table for our examples
CREATE TABLE employees (
employee_id SERIAL PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
department VARCHAR(50),
salary NUMERIC(10, 2)
);
-- Insert some sample data
INSERT INTO employees (first_name, last_name, department, salary)
VALUES
('John', 'Doe', 'IT', 75000),
('Jane', 'Smith', 'HR', 65000),
('Bob', 'Johnson', 'IT', 80000),
('Alice', 'Williams', 'Finance', 90000),
('Charlie', 'Brown', 'HR', 60000);
-- Create a simple view
CREATE VIEW it_department AS
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE department = 'IT';
Now we can query this view just like a regular table:
-- Query the view
SELECT * FROM it_department;
Result:
employee_id | first_name | last_name | salary
-------------+------------+-----------+--------
1 | John | Doe | 75000.00
3 | Bob | Johnson | 80000.00
Creating Views with Calculated Fields
Views can include calculations and function calls:
-- Create a view with calculated fields
CREATE VIEW employee_details AS
SELECT
employee_id,
first_name || ' ' || last_name AS full_name,
department,
salary,
salary * 0.2 AS bonus
FROM employees;
Querying this view:
SELECT * FROM employee_details;
Result:
employee_id | full_name | department | salary | bonus
-------------+-----------------+------------+----------+----------
1 | John Doe | IT | 75000.00 | 15000.00
2 | Jane Smith | HR | 65000.00 | 13000.00
3 | Bob Johnson | IT | 80000.00 | 16000.00
4 | Alice Williams | Finance | 90000.00 | 18000.00
5 | Charlie Brown | HR | 60000.00 | 12000.00
Creating Views with Joins
Views are particularly useful for simplifying complex joins:
-- Create departments table
CREATE TABLE departments (
department_id SERIAL PRIMARY KEY,
department_name VARCHAR(50),
location VARCHAR(100)
);
-- Insert sample data
INSERT INTO departments (department_name, location)
VALUES
('IT', 'Building A'),
('HR', 'Building B'),
('Finance', 'Building C');
-- Create a view with a join
CREATE VIEW employee_department_details AS
SELECT
e.employee_id,
e.first_name,
e.last_name,
e.salary,
d.department_name,
d.location
FROM
employees e
JOIN
departments d ON e.department = d.department_name;
Querying the view:
SELECT * FROM employee_department_details;
Result:
employee_id | first_name | last_name | salary | department_name | location
-------------+------------+-----------+----------+-----------------+-----------
1 | John | Doe | 75000.00 | IT | Building A
2 | Jane | Smith | 65000.00 | HR | Building B
3 | Bob | Johnson | 80000.00 | IT | Building A
4 | Alice | Williams | 90000.00 | Finance | Building C
5 | Charlie | Brown | 60000.00 | HR | Building B
Using OR REPLACE to Update a View
You can modify an existing view using the OR REPLACE clause:
-- Update the it_department view to include department
CREATE OR REPLACE VIEW it_department AS
SELECT employee_id, first_name, last_name, department, salary
FROM employees
WHERE department = 'IT';
Now when you query the updated view:
SELECT * FROM it_department;
Result:
employee_id | first_name | last_name | department | salary
-------------+------------+-----------+------------+----------
1 | John | Doe | IT | 75000.00
3 | Bob | Johnson | IT | 80000.00
Creating Views with Check Option
The WITH CHECK OPTION clause ensures that operations on the view follow the view's defining condition:
-- Create a view with CHECK OPTION
CREATE VIEW high_salary_employees AS
SELECT *
FROM employees
WHERE salary > 70000
WITH CHECK OPTION;
If you try to insert a record through this view that doesn't meet the condition:
-- This will fail because of CHECK OPTION
INSERT INTO high_salary_employees
(first_name, last_name, department, salary)
VALUES ('Mark', 'Taylor', 'Finance', 65000);
PostgreSQL will return an error:
ERROR: new row violates check option for view "high_salary_employees"
DETAIL: Failing row contains (6, Mark, Taylor, Finance, 65000.00).
But this will work:
-- This will work because salary > 70000
INSERT INTO high_salary_employees
(first_name, last_name, department, salary)
VALUES ('Mark', 'Taylor', 'Finance', 75000);
Creating Recursive Views
PostgreSQL supports recursive views through Common Table Expressions (CTEs):
-- Create a table for hierarchical data
CREATE TABLE employees_hierarchy (
employee_id SERIAL PRIMARY KEY,
name VARCHAR(100),
manager_id INTEGER REFERENCES employees_hierarchy(employee_id)
);
-- Insert sample data
INSERT INTO employees_hierarchy (employee_id, name, manager_id)
VALUES
(1, 'CEO', NULL),
(2, 'CTO', 1),
(3, 'CFO', 1),
(4, 'Developer 1', 2),
(5, 'Developer 2', 2),
(6, 'Accountant', 3);
-- Create a recursive view
CREATE VIEW employee_hierarchy AS
WITH RECURSIVE emp_hierarchy AS (
-- Base case: employees with no manager (CEO)
SELECT employee_id, name, manager_id, 0 AS level, name::text AS path
FROM employees_hierarchy
WHERE manager_id IS NULL
UNION ALL
-- Recursive case: join employees with their managers
SELECT e.employee_id, e.name, e.manager_id, h.level + 1, h.path || ' > ' || e.name
FROM employees_hierarchy e
JOIN emp_hierarchy h ON e.manager_id = h.employee_id
)
SELECT employee_id, name, manager_id, level, path FROM emp_hierarchy;
Querying the recursive view:
SELECT * FROM employee_hierarchy ORDER BY level, employee_id;
Result:
employee_id | name | manager_id | level | path
-------------+-------------+------------+-------+---------------------
1 | CEO | | 0 | CEO
2 | CTO | 1 | 1 | CEO > CTO
3 | CFO | 1 | 1 | CEO > CFO
4 | Developer 1 | 2 | 2 | CEO > CTO > Developer 1
5 | Developer 2 | 2 | 2 | CEO > CTO > Developer 2
6 | Accountant | 3 | 2 | CEO > CFO > Accountant
View Information Schema
You can query information about your views from the PostgreSQL information_schema:
-- Get information about views
SELECT table_name, view_definition
FROM information_schema.views
WHERE table_schema = 'public';
This will show all the views you've created along with their definitions.
Dropping a View
To remove a view, use the DROP VIEW statement:
DROP VIEW view_name;
-- For example
DROP VIEW it_department;
To avoid errors if the view doesn't exist, you can use:
DROP VIEW IF EXISTS view_name;
Real-World Applications
1. Data Security
Views can be used to restrict access to sensitive data:
-- Create a secure view for HR users
CREATE VIEW hr_employee_view AS
SELECT employee_id, first_name, last_name, department
FROM employees;
-- Grant permissions on the view but not on the underlying table
GRANT SELECT ON hr_employee_view TO hr_users;
This way, HR users can see employee information but not their salaries.
2. Business Reporting
Views can simplify complex reporting queries:
-- Create a department summary view for reporting
CREATE VIEW department_summary AS
SELECT
department,
COUNT(*) AS employee_count,
MIN(salary) AS min_salary,
MAX(salary) AS max_salary,
AVG(salary) AS avg_salary,
SUM(salary) AS total_salary_budget
FROM employees
GROUP BY department;
Business analysts can now easily get department statistics without writing complex queries.
3. Data Transformation
Views can present data in a more user-friendly format:
-- Create a customer-friendly view
CREATE VIEW customer_friendly_orders AS
SELECT
o.order_id,
c.name AS customer_name,
p.name AS product_name,
o.quantity,
o.order_date,
CASE
WHEN o.status = 'P' THEN 'Pending'
WHEN o.status = 'S' THEN 'Shipped'
WHEN o.status = 'D' THEN 'Delivered'
ELSE 'Unknown'
END AS status
FROM
orders o
JOIN
customers c ON o.customer_id = c.customer_id
JOIN
products p ON o.product_id = p.product_id;
This view transforms cryptic status codes into human-readable text.
Limitations of Views
While views are powerful, they have some limitations:
-
Performance: Complex views with multiple joins or subqueries may not perform as well as optimized direct queries.
-
Indexing: You cannot create indexes directly on views (though PostgreSQL has materialized views for this purpose).
-
Constraints: Not all constraints can be applied to views.
-
Updates: Not all views are updatable. Views with joins, aggregations,
DISTINCT,GROUP BY, etc., generally cannot be updated.
Summary
PostgreSQL views are powerful database objects that provide a way to abstract complex queries, enhance security, and present data in customized formats. By creating a view, you essentially save a query that can be referenced like a regular table.
Key points to remember:
- Views don't store data themselves; they query the underlying tables when accessed
- Views can simplify complex queries with joins and calculations
- Views can enhance security by restricting access to specific columns
- The
WITH CHECK OPTIONensures data integrity when modifying data through views - Not all views are updatable, especially those with complex operations
- Views have some performance limitations compared to direct table access
Exercises
-
Create a view that shows employees and their departments with a salary higher than the average salary of their department.
-
Create a view that joins three tables: customers, orders, and products, showing all orders with customer and product details.
-
Create a view with
WITH CHECK OPTIONthat only shows active users, then try to insert an inactive user through the view. -
Create a recursive view showing a file system hierarchy (folders and subfolders).
-
Try to update a view that has a join and observe what happens.
Additional Resources
If you spot any mistakes on this website, please let me know at [email protected]. I’d greatly appreciate your feedback! :)