Views (`VIEW`) in PostgreSQL

Sometimes SQL queries get way too big, complicated, and repetitive. Like, you’re building a report on students with their average grades and number of courses — and you end up writing the same subquery in five different places. Instead of copy-pasting the same thing over and over, use views — or VIEW.

VIEW is like a “saved query” that you can give a name and use just like a table. It doesn’t store data, just the query structure.

This is especially handy when you want to:

• make your SQL code easier to read;
• reuse a complex selection;
• hide technical details from users;
• control access levels (show only the data you want).

How does VIEW work?

Creating a view means making a pseudo-table based on a SELECT query. For example:

CREATE VIEW student_avg_grades AS
SELECT 
    s.student_id,
    s.name,
    AVG(g.grade) AS avg_grade
FROM 
    students s
JOIN 
    grades g ON s.student_id = g.student_id
GROUP BY 
    s.student_id, s.name;

Now you can use student_avg_grades just like a regular table:

SELECT * FROM student_avg_grades WHERE avg_grade > 4.5;

University Example

You often analyze students, their courses, and grades. Let’s say you need to build a summary report for each student:

• name,
• number of course enrollments,
• average grade.

If you try to do this all at once, you’ll get a long and unreadable SQL. It’s way easier to break it up: first, create two views (VIEW), then combine them.

Table students

Table enrollments

1. View: course count

This view counts how many courses each student has:

CREATE VIEW student_course_count AS
SELECT 
    student_id,
    COUNT(*) AS course_count
FROM 
    enrollments
GROUP BY student_id;

Result:

2. View: average grade And this one — the student’s average grade (if there’s no grade — NULL, and it doesn’t count):

CREATE VIEW student_avg_grade AS
SELECT
student_id,
AVG(grade) AS avg_grade
FROM
enrollments
WHERE grade IS NOT NULL
GROUP BY student_id;

3. Using these VIEWs

SELECT
    s.name,
    c.course_count,
    a.avg_grade
FROM 
    students s
LEFT JOIN student_course_count c ON s.student_id = c.student_id
LEFT JOIN student_avg_grade a ON s.student_id = a.student_id;

Now you’ve got a clean and readable query. And the best part — it’s super easy to maintain.

Updatability of VIEW

Views usually don’t store data — they’re just a "wrapper" around a query. But you can even use them to update data, if your VIEW meets a few conditions (like, no JOIN, GROUP BY, aggregates, or subqueries).

Example of an updatable view:

CREATE VIEW active_students AS
SELECT * FROM students WHERE active = true;

Now you can do:

UPDATE active_students SET name = 'Ivan Petrov' WHERE student_id = 2;

But if your VIEW is complicated (like, it has a JOIN or GROUP BY), it becomes read-only. For updates, you can use INSTEAD OF triggers, but that’s more advanced stuff.

How to drop or change a VIEW

Drop:

DROP VIEW student_avg_grade;

Update:

CREATE OR REPLACE VIEW student_avg_grade AS
SELECT student_id, ROUND(AVG(grade), 2) AS avg_grade
FROM grades
GROUP BY student_id;

Examples: when VIEW is especially useful

1. Building reports

One VIEW — one logic. You can set up report_enrollments_by_month, report_payments_by_year, and so on.

2. Access control

Create a VIEW that only shows limited data (like, without personal info) and give access only to that.

3. Reuse

Instead of copy-pasting a subquery — just give it a name as a VIEW.

Tips

• Give clear names: view_avg_scores_by_group, active_users_view — makes it way easier to read.
• Don’t make chains of VIEW in VIEW in VIEW — that just makes things harder to understand and debug.
• Watch performance: PostgreSQL will recalculate the VIEW every time you query it, unless it’s a MATERIALIZED VIEW.

MATERIALIZED VIEW

Unlike a regular VIEW (which is recomputed on every query), a MATERIALIZED VIEW physically stores its result as a table. Upside — fast reads; downside — the data can go stale and has to be refreshed manually with REFRESH MATERIALIZED VIEW view_name. Use it for heavy analytical queries where you don’t need real-time freshness.

CREATE MATERIALIZED VIEW student_course_count_mv AS
SELECT student_id, COUNT(*) AS course_count
FROM enrollments
GROUP BY student_id;

-- Later, when the data has changed:
REFRESH MATERIALIZED VIEW student_course_count_mv;

VIEW is one of the simplest and handiest tools in PostgreSQL. You don’t need to learn anything new, just rethink: “Hey, maybe I should save this query and use it like a table?” If your query is getting bulky — chances are, it deserves to be a VIEW.