PostgreSQL Views

A view is a saved SQL query that behaves like a virtual table. Instead of typing a long or complex query every time, a view stores that query and lets it be referenced by name — just like a regular table. The data displayed by a view is not stored separately; it is generated fresh from the underlying tables each time the view is queried.

Why Use Views?

Simplify Complex Queries

A query with multiple JOINs, filters, and calculations can be wrapped in a view. After that, accessing the data requires only a simple SELECT on the view name.

Improve Security

Views can expose only specific columns or rows from a table. A sensitive table like employees might contain salary data, but a view can be created that shows only name and department — and users can be granted access to the view instead of the full table.

Maintain Consistency

When multiple applications or reports run the same logic, embedding that logic in a view ensures everyone uses the same calculation. If the logic needs to change, it only needs to be updated in one place.

Creating a View

Basic Syntax

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

Example

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

After this, querying active employees is as simple as:

SELECT * FROM active_employees;

This returns the same result as running the full query with the WHERE clause, but through a clean, reusable name.

Querying a View

Views are queried exactly like tables:

-- Select specific columns
SELECT name, salary FROM active_employees;

-- Filter the view
SELECT * FROM active_employees WHERE department = 'IT';

-- Sort the view
SELECT name, department FROM active_employees ORDER BY department;

Additional WHERE clauses and ORDER BY can be added on top of the view just like any other table query.

Views with JOINs

CREATE VIEW order_details AS
SELECT
    o.id AS order_id,
    c.name AS customer_name,
    o.product,
    o.amount
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id;

Now the joined result is accessible through a clean view name:

SELECT * FROM order_details WHERE amount > 500;

Modifying a View with CREATE OR REPLACE

To update an existing view's definition without dropping it first:

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

The new definition replaces the old one. Any permissions granted on the view are preserved. Note: the new SELECT must include all the original columns (additional columns can be added, but existing ones cannot be removed or reordered).

Dropping a View

DROP VIEW active_employees;

This removes the view definition. The underlying table and its data are completely unaffected.

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

Listing All Views

-- In psql
\dv

-- Or query the information schema
SELECT table_name FROM information_schema.views
WHERE table_schema = 'public';

Updatable Views

A simple view (one table, no aggregates, no DISTINCT, no GROUP BY) can be used directly with INSERT, UPDATE, and DELETE. The changes are applied to the underlying table.

-- Simple view
CREATE VIEW it_employees AS
SELECT id, name, salary FROM employees WHERE department = 'IT';

-- Update through the view
UPDATE it_employees SET salary = 80000 WHERE name = 'Ben';

This updates the salary column in the actual employees table for Ben. Complex views (with JOINs, aggregates, or DISTINCT) are not updatable.

Materialized Views

A regular view re-runs its query every time it is accessed. A materialized view stores the query result physically on disk. This is faster for complex, slow queries — but the stored data becomes stale until it is manually refreshed.

Creating a Materialized View

CREATE MATERIALIZED VIEW department_summary AS
SELECT department,
       COUNT(*) AS headcount,
       ROUND(AVG(salary), 2) AS avg_salary
FROM employees
GROUP BY department;

Querying a Materialized View

SELECT * FROM department_summary;

Refreshing a Materialized View

-- Refresh the stored data (locks the view during refresh)
REFRESH MATERIALIZED VIEW department_summary;

-- Refresh without locking (allows concurrent reads)
REFRESH MATERIALIZED VIEW CONCURRENTLY department_summary;

CONCURRENTLY requires a unique index on the materialized view and was introduced in PostgreSQL 9.4. It is the preferred option in production systems where downtime is not acceptable.

Dropping a Materialized View

DROP MATERIALIZED VIEW department_summary;

Regular View vs Materialized View

Example: Views in Practice

-- Base table
CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    salesperson VARCHAR(100),
    region VARCHAR(50),
    amount NUMERIC(10, 2),
    sale_date DATE
);

-- Create a view for monthly summary
CREATE VIEW monthly_sales_summary AS
SELECT
    DATE_TRUNC('month', sale_date) AS month,
    region,
    SUM(amount) AS total_sales,
    COUNT(*) AS num_transactions
FROM sales
GROUP BY DATE_TRUNC('month', sale_date), region;

-- Use the view
SELECT * FROM monthly_sales_summary ORDER BY month, total_sales DESC;

-- View to hide sensitive details
CREATE VIEW public_sales AS
SELECT salesperson, region, sale_date
FROM sales;

SELECT * FROM public_sales WHERE region = 'North';

Key Points

• A view is a saved SQL query referenced by name as a virtual table.
• Views simplify complex queries, improve security, and promote code reuse.
• Views are queried just like regular tables and can be filtered and sorted.
• CREATE OR REPLACE VIEW updates a view's definition without dropping it.
• Simple views can be used with INSERT, UPDATE, and DELETE (updatable views).
• Materialized views store query results on disk for faster access but must be manually refreshed.