Creating and Using Views

A view is a named query that you can reference like a table. Think of it as a saved SELECT statement with a name. Views simplify complex queries, provide a layer of abstraction, and can enhance security by restricting what data users can see.

Why Use Views?

Views solve several common problems:

1. Simplify complex queries: Write the complex query once, then SELECT from the view
2. Abstraction: Hide the underlying table structure from application code
3. Security: Expose only certain columns or rows to specific users
4. Consistency: Ensure everyone uses the same query logic
5. Maintenance: Change the underlying query in one place instead of updating every application

Creating a View

Basic Syntax

CREATE VIEW view_name AS
SELECT columns
FROM tables
WHERE conditions;

Example: Active Employees View

CREATE VIEW active_employees AS
SELECT name, department, salary, email
FROM employees
WHERE is_active = true;

Now you can query it like a table:

SELECT * FROM active_employees;

SELECT name, salary
FROM active_employees
WHERE department = 'Engineering';

Example: Department Summary View

CREATE VIEW department_summary AS
SELECT
  department,
  COUNT(*) AS employee_count,
  AVG(salary) AS avg_salary,
  MIN(salary) AS min_salary,
  MAX(salary) AS max_salary
FROM employees
GROUP BY department;

-- Simple to query now
SELECT * FROM department_summary
WHERE avg_salary > 70000;

Example: Sales Report View

CREATE VIEW monthly_sales_report AS
SELECT
  EXTRACT(YEAR FROM sale_date) AS sale_year,
  EXTRACT(MONTH FROM sale_date) AS sale_month,
  region,
  COUNT(*) AS num_sales,
  SUM(amount) AS total_amount,
  AVG(amount) AS avg_amount
FROM sales
GROUP BY
  EXTRACT(YEAR FROM sale_date),
  EXTRACT(MONTH FROM sale_date),
  region
ORDER BY sale_year, sale_month, region;

Users can now run simple queries against this view without knowing the underlying aggregation logic:

SELECT * FROM monthly_sales_report
WHERE region = 'North';

Querying Views

Views behave exactly like tables in SELECT statements. You can:

Filter with WHERE

SELECT * FROM active_employees
WHERE salary > 70000;

Join with Other Tables or Views

SELECT ae.name, ae.department, ds.avg_salary
FROM active_employees ae
JOIN department_summary ds ON ae.department = ds.department;

Use in Subqueries

SELECT * FROM employees
WHERE department IN (
  SELECT department FROM department_summary
  WHERE employee_count > 2
);

Apply Aggregates

SELECT department, COUNT(*)
FROM active_employees
GROUP BY department;

CREATE OR REPLACE VIEW

If you need to modify a view's definition, use CREATE OR REPLACE VIEW:

CREATE OR REPLACE VIEW active_employees AS
SELECT name, department, salary, email, hire_date  -- added hire_date
FROM employees
WHERE is_active = true;

Important restrictions:

• The new query must return the same columns in the same order with the same names and types
• You can add new columns at the end
• You cannot remove columns or change their names/types

If you need to make incompatible changes, drop and recreate the view.

Updatable Views

PostgreSQL allows INSERT, UPDATE, and DELETE on simple views that meet these criteria:

1. The view selects from exactly one table (no joins)
2. The view does not use GROUP BY, HAVING, DISTINCT, or set operations
3. The view does not use aggregate functions or window functions
4. Every column in the view is a simple column reference (no expressions)

Example

CREATE VIEW engineering_team AS
SELECT name, salary, email, is_active
FROM employees
WHERE department = 'Engineering';

This view is updatable:

-- Update through the view
UPDATE engineering_team
SET salary = salary * 1.10
WHERE name = 'Alice';

-- Insert through the view
INSERT INTO engineering_team (name, salary, email, is_active)
VALUES ('New Person', 80000, 'new@example.com', true);

The insert above will work, but the new row will have department set to NULL (since it is not in the view), which means it won't appear in the view!

WITH CHECK OPTION

WITH CHECK OPTION prevents inserting or updating rows through a view that would not be visible through the view.

CREATE VIEW engineering_team AS
SELECT name, department, salary, email, is_active
FROM employees
WHERE department = 'Engineering'
WITH CHECK OPTION;

Now this would fail:

-- ERROR: new row violates check option for view "engineering_team"
INSERT INTO engineering_team (name, department, salary, email, is_active)
VALUES ('Sales Person', 'Sales', 60000, 'sales@example.com', true);

The insert is rejected because the new row has department = 'Sales', which would not satisfy the view's WHERE department = 'Engineering' condition.

LOCAL vs CASCADED

When views are built on top of other views:

• WITH LOCAL CHECK OPTION: Only checks the current view's conditions
• WITH CASCADED CHECK OPTION (default): Checks conditions in the current view AND all underlying views

CREATE VIEW high_paid_engineers AS
SELECT * FROM engineering_team
WHERE salary > 80000
WITH CASCADED CHECK OPTION;

An insert through this view must satisfy both department = 'Engineering' AND salary > 80000.

Dropping Views

-- Drop a view
DROP VIEW active_employees;

-- Drop only if it exists (no error if missing)
DROP VIEW IF EXISTS active_employees;

-- Drop a view and all views that depend on it
DROP VIEW department_summary CASCADE;

Be careful with CASCADE -- it will drop any views built on top of the one you are dropping.

View Dependencies

Views depend on the underlying tables. If you try to drop a table that a view references, PostgreSQL will stop you:

-- This would fail if a view depends on the table
DROP TABLE employees;
-- ERROR: cannot drop table employees because other objects depend on it

-- Use CASCADE to drop the table and all dependent views
DROP TABLE employees CASCADE;

Listing Views

-- See all views in the current schema
SELECT table_name, view_definition
FROM information_schema.views
WHERE table_schema = 'public';

Best Practices

1. Name views clearly: Use descriptive names like active_employees, monthly_sales_report
2. Document complex views: Add comments with COMMENT ON VIEW
3. Avoid deep nesting: Views built on views built on views can be hard to debug and may hurt performance
4. Consider performance: Views do not cache results -- the underlying query runs every time. For expensive queries, consider materialized views (next lesson)
5. Use views for security: Grant access to the view without granting access to the underlying table

-- Add a comment to document a view
COMMENT ON VIEW monthly_sales_report IS
  'Monthly sales aggregation by region. Used by the reporting dashboard.';

Try It Yourself

Think about the following scenarios and consider how you would design views:

1. A view that shows only employees and their managers (by joining employees to itself on manager_id).

2. A view that shows sales with a "high_value" label for amounts above $200 and "standard" for the rest.

3. An updatable view for a specific department with WITH CHECK OPTION to prevent inserting employees from other departments.