Views

A view is a stored query that acts like a virtual table. When you create a view, you are not creating a new table — you are saving a SELECT statement that can be referenced like a table. Views simplify complex queries and add a layer of security.

Creating a View

The syntax is straightforward:

CREATE VIEW student_course_summary AS
SELECT
  s.id,
  s.first_name,
  s.last_name,
  COUNT(e.id) AS course_count,
  ROUND(AVG(CASE e.grade
    WHEN 'A' THEN 4 WHEN 'B' THEN 3
    WHEN 'C' THEN 2 WHEN 'D' THEN 1 ELSE 0
  END), 2) AS avg_gpa
FROM students s
LEFT JOIN enrollments e ON s.id = e.student_id
GROUP BY s.id, s.first_name, s.last_name;

Now you can query the view like a regular table:

SELECT * FROM student_course_summary
WHERE avg_gpa > 3.5
ORDER BY avg_gpa DESC;

The complex query with JOINs and aggregations is hidden behind the view. Your application code just sees a simple table.

Views for Security

Views can restrict access to certain columns or rows:

CREATE VIEW public_student_info AS
SELECT
  id,
  first_name,
  last_name,
  enrollment_date
FROM students;

-- Grant access only to the view, not the underlying table
GRANT SELECT ON public_student_info TO app_user;
REVOKE SELECT ON students FROM app_user;

Now the app_user can see basic student information but cannot access sensitive columns like email or date of birth.

Modifying and Dropping Views

To change a view's definition:

CREATE OR REPLACE VIEW student_course_summary AS
SELECT
  s.id,
  s.first_name,
  s.last_name,
  COUNT(e.id) AS course_count
FROM students s
LEFT JOIN enrollments e ON s.id = e.student_id
GROUP BY s.id, s.first_name, s.last_name;

To remove a view:

DROP VIEW IF EXISTS student_course_summary;

Dropping a view does not affect the underlying tables or data. It only removes the saved query.

Updatable Views

Simple views can be used for INSERT, UPDATE, and DELETE operations:

CREATE VIEW active_students AS
SELECT id, first_name, last_name, email
FROM students
WHERE status = 'active';

-- This works because it maps directly to the students table
UPDATE active_students
SET email = 'newemail@example.com'
WHERE id = 1;

However, views with JOINs, aggregations, GROUP BY, or DISTINCT are generally not updatable. Use triggers instead for complex update logic.

Materialized Views

Regular views execute the underlying query every time you access them. Materialized views store the results physically:

CREATE MATERIALIZED VIEW department_stats AS
SELECT
  department_id,
  COUNT(*) AS student_count,
  AVG(gpa) AS avg_gpa
FROM students
GROUP BY department_id;

-- Refresh when data changes
REFRESH MATERIALIZED VIEW department_stats;

Materialized views are great for expensive queries that do not need to be real-time. The trade-off is that you must refresh them manually (or on a schedule) to keep them up to date.

When to Use Views

Views are useful when you:

• Have complex queries that are used in multiple places
• Want to simplify the data model for application code
• Need to restrict access to certain columns or rows
• Want to provide a stable interface even if the underlying schema changes