Offensive Security Researcher & Quantum Cryptography Analyst

How to Create Views in PostgreSQL

Abdul Wahab Junaid
How to Create Views in PostgreSQL

There’s a particular moment in every database project where the same complicated join keeps showing up in query after query, and someone on the team finally says, “why don’t we just save this as something we can reuse?” That’s usually the moment views enter the picture. I use them constantly — for simplifying complex queries, for controlling access to sensitive columns, and for giving reporting tools a clean, stable interface to query against. In this guide, I’ll cover everything from the basic syntax to materialized views and the gotchas that catch people off guard.

What Is a View in PostgreSQL?

A view is a stored SQL query that behaves like a virtual table. It doesn’t store data itself (with one important exception, covered later) — instead, every time you query a view, PostgreSQL runs the underlying query and returns the result as if it were a table.

Views are useful for:

Basic Syntax

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

Here’s a simple example:

CREATE VIEW active_employees AS
SELECT id, name, department, hire_date
FROM employees
WHERE status = 'active';

Now you can query it just like a table:

SELECT * FROM active_employees WHERE department = 'Engineering';

PostgreSQL takes care of combining your view’s query with your SELECT statement’s conditions behind the scenes.

Creating Views with Joins

Views really shine when they hide the complexity of multi-table joins:

CREATE VIEW employee_details AS
SELECT
    e.id,
    e.name,
    d.department_name,
    s.salary
FROM employees e
JOIN departments d ON e.department_id = d.id
JOIN salaries s ON e.id = s.employee_id;

Now anyone querying employee_details gets a clean, joined result without needing to know or repeat the join logic themselves:

SELECT name, department_name FROM employee_details WHERE salary > 80000;

Creating Views with Aggregations

CREATE VIEW department_headcount AS
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;

SELECT * FROM department_headcount ORDER BY employee_count DESC;

Updating and Replacing Views

If you need to modify a view’s definition, you have two options. CREATE OR REPLACE VIEW works as long as you’re not changing the output column names, order, or removing columns:

CREATE OR REPLACE VIEW active_employees AS
SELECT id, name, department, hire_date, email
FROM employees
WHERE status = 'active';

If you need to remove a column or fundamentally restructure the view, you’ll need to drop and recreate it:

DROP VIEW active_employees;

CREATE VIEW active_employees AS
SELECT id, name, hire_date
FROM employees
WHERE status = 'active';

Be careful with DROP VIEW if other views or objects depend on it — PostgreSQL will block the drop unless you use CASCADE, which then drops those dependents too.

Updatable Views

Here’s something that surprises people who assume views are always read-only: PostgreSQL supports automatically updatable views for simple cases. If a view is based on a single table (no joins, aggregates, DISTINCT, GROUP BY, or set operations), you can INSERT, UPDATE, and DELETE directly through it:

CREATE VIEW active_employees AS
SELECT id, name, department, hire_date
FROM employees
WHERE status = 'active';

UPDATE active_employees SET department = 'Sales' WHERE id = 42;

This actually updates the underlying employees table. For more complex views (joins, aggregates), you’d need to define INSTEAD OF triggers to make them updatable, which gives you full control over what happens when someone tries to insert or update through the view.

CREATE OR REPLACE FUNCTION update_employee_details()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE employees SET name = NEW.name WHERE id = NEW.id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER employee_details_update
INSTEAD OF UPDATE ON employee_details
FOR EACH ROW EXECUTE FUNCTION update_employee_details();

Views with Security: WITH CHECK OPTION

If you’re using an updatable view to restrict what rows a role can modify, WITH CHECK OPTION prevents inserts or updates that would create rows falling outside the view’s WHERE clause:

CREATE VIEW active_employees AS
SELECT id, name, department, status
FROM employees
WHERE status = 'active'
WITH CHECK OPTION;

Without this, someone could update a row through the view in a way that changes its status to 'inactive', and the row would simply vanish from the view’s results — which is often not what you want. WITH CHECK OPTION blocks that kind of update outright.

Security Barrier Views

For views used specifically to restrict access to sensitive data, security_barrier prevents a subtle vulnerability where a malicious or careless function used in a filtering condition could leak data from rows that should be hidden:

CREATE VIEW public_employee_info WITH (security_barrier = true) AS
SELECT id, name, department
FROM employees
WHERE department != 'Executive';

Materialized Views

Unlike regular views, a materialized view actually stores the query result physically on disk, like a snapshot. This is useful when the underlying query is expensive and doesn’t need to reflect real-time data.

CREATE MATERIALIZED VIEW monthly_sales_summary AS
SELECT
    date_trunc('month', sale_date) AS month,
    SUM(amount) AS total_sales
FROM sales
GROUP BY date_trunc('month', sale_date);

Querying it is fast because the data is already computed:

SELECT * FROM monthly_sales_summary ORDER BY month;

But the data goes stale as the underlying sales table changes. You need to refresh it manually or on a schedule:

REFRESH MATERIALIZED VIEW monthly_sales_summary;

To avoid locking readers out during the refresh, use:

REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales_summary;

Note that CONCURRENTLY requires a unique index on the materialized view first:

CREATE UNIQUE INDEX idx_monthly_sales_month ON monthly_sales_summary(month);

Common Use Cases

Troubleshooting Common Issues

“cannot drop view because other objects depend on it” — another view, function, or table constraint references this one. Use DROP VIEW view_name CASCADE if you’re sure, but check dependencies first with:

SELECT dependent_ns.nspname, dependent_view.relname
FROM pg_depend
JOIN pg_rewrite ON pg_depend.objid = pg_rewrite.oid
JOIN pg_class AS dependent_view ON pg_rewrite.ev_class = dependent_view.oid
JOIN pg_class AS source_table ON pg_depend.refobjid = source_table.oid
JOIN pg_namespace dependent_ns ON dependent_view.relnamespace = dependent_ns.oid
WHERE source_table.relname = 'employees';

View is slow — remember a regular view is just a stored query; it inherits all the performance characteristics (and problems) of the underlying SQL. If it’s slow, optimize the query itself (indexes, join order) or consider converting it to a materialized view if real-time freshness isn’t required.

“cannot update view” errors — the view isn’t automatically updatable because it involves a join, aggregate, or DISTINCT. You’ll need INSTEAD OF triggers, or update the base tables directly.

Materialized view shows stale data — this is expected; it only reflects data as of the last REFRESH. Schedule refreshes with a cron job or pg_cron extension if you need it kept current.

Best Practices

Wrapping Up

Views are one of those PostgreSQL features that feel almost too simple at first — just a saved query — until you realize how much complexity they can absorb on behalf of everyone querying your database. Whether you’re simplifying joins, controlling access to sensitive data, or precomputing expensive aggregations with materialized views, the underlying idea is the same: define the logic once, reuse it everywhere, and keep your actual queries clean.

Exit mobile version