Eric Campbell 6 min read PostgreSQL

How to Use Views in PostgreSQL

Views in PostgreSQL are saved SQL queries that act as virtual tables. When you query a view, PostgreSQL executes the underlying SQL statement and returns the results as if they were coming from a...

Key Insights

  • Views act as virtual tables that encapsulate complex queries, providing data abstraction and simplifying application code while regular views execute underlying queries on each access
  • Updatable views allow INSERT, UPDATE, and DELETE operations when they meet specific conditions like referencing a single table without aggregations or DISTINCT clauses
  • Materialized views physically store query results for expensive operations but require manual or scheduled refreshes to maintain data accuracy

Introduction to PostgreSQL Views

Views in PostgreSQL are saved SQL queries that act as virtual tables. When you query a view, PostgreSQL executes the underlying SQL statement and returns the results as if they were coming from a real table. This abstraction layer provides several practical benefits: you can hide complex joins and business logic, restrict access to specific columns for security purposes, and maintain a stable interface even when underlying table structures change.

The key distinction to understand upfront is between regular views and materialized views. Regular views execute their defining query every time you access them—they’re essentially query shortcuts with no data storage overhead. Materialized views, on the other hand, physically store the query results on disk, trading freshness for performance when dealing with expensive computations.

Use regular views when you need real-time data and the underlying queries are reasonably fast. Choose materialized views for complex aggregations, reporting queries, or when you can tolerate slightly stale data in exchange for dramatic performance improvements.

Creating Basic Views

The syntax for creating a view is straightforward: CREATE VIEW view_name AS SELECT .... Let’s build a practical example using an e-commerce scenario with users and orders.

-- Base tables
CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    full_name VARCHAR(255),
    status VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(user_id),
    order_total DECIMAL(10,2),
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(20)
);

-- Create a view for active customers with their order summary
CREATE VIEW active_customers AS
SELECT 
    u.user_id,
    u.email,
    u.full_name,
    COUNT(o.order_id) AS total_orders,
    COALESCE(SUM(o.order_total), 0) AS lifetime_value,
    MAX(o.order_date) AS last_order_date
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
WHERE u.status = 'active'
GROUP BY u.user_id, u.email, u.full_name;

This view encapsulates business logic that would otherwise be repeated across your application. Instead of writing this join and aggregation logic everywhere, your application code simply queries active_customers.

Querying and Managing Views

Once created, views behave like regular tables for SELECT operations:

-- Query the view like any table
SELECT * FROM active_customers 
WHERE lifetime_value > 1000 
ORDER BY last_order_date DESC;

-- Views work with JOINs too
SELECT ac.full_name, ac.total_orders, o.order_id
FROM active_customers ac
JOIN orders o ON ac.user_id = o.user_id
WHERE o.status = 'pending';

PostgreSQL provides several ways to inspect and manage views:

-- List all views in the current schema (psql command)
\dv

-- Get detailed information about a specific view
\d+ active_customers

-- View the underlying SQL definition
SELECT definition FROM pg_views 
WHERE viewname = 'active_customers';

-- Rename a view
ALTER VIEW active_customers RENAME TO active_customer_summary;

-- Drop a view
DROP VIEW IF EXISTS active_customer_summary;

-- Drop with CASCADE to remove dependent objects
DROP VIEW active_customers CASCADE;

The CASCADE option is critical when other views or database objects depend on the view you’re dropping. Without it, PostgreSQL will refuse to drop a view that has dependencies.

Updatable Views

Not all views are read-only. PostgreSQL allows INSERT, UPDATE, and DELETE operations on views that meet specific criteria. A view is automatically updatable when it:

  • References exactly one table
  • Contains no aggregate functions, GROUP BY, HAVING, LIMIT, or OFFSET clauses
  • Doesn’t use DISTINCT, UNION, INTERSECT, or EXCEPT
  • Includes all NOT NULL columns without defaults from the base table

Here’s a practical example:

-- Create a simple updatable view
CREATE VIEW active_users AS
SELECT user_id, email, full_name, created_at
FROM users
WHERE status = 'active';

-- Insert through the view
INSERT INTO active_users (email, full_name)
VALUES ('john@example.com', 'John Smith');

-- This actually inserts into the users table
-- Note: status won't be 'active' unless you use WITH CHECK OPTION

-- Update through the view
UPDATE active_users 
SET full_name = 'John A. Smith'
WHERE email = 'john@example.com';

-- Use WITH CHECK OPTION to enforce the view's WHERE clause
CREATE OR REPLACE VIEW active_users AS
SELECT user_id, email, full_name, created_at
FROM users
WHERE status = 'active'
WITH CHECK OPTION;

-- Now this would fail because status isn't 'active'
INSERT INTO active_users (email, full_name)
VALUES ('jane@example.com', 'Jane Doe');

The WITH CHECK OPTION clause ensures that any INSERT or UPDATE through the view maintains the view’s filter condition. This prevents you from creating rows that wouldn’t appear in the view.

Materialized Views

When dealing with expensive queries—complex joins, large aggregations, or calculations across millions of rows—materialized views provide substantial performance benefits by storing the query results physically.

-- Create a materialized view for reporting
CREATE MATERIALIZED VIEW monthly_sales_summary AS
SELECT 
    DATE_TRUNC('month', order_date) AS month,
    COUNT(*) AS order_count,
    SUM(order_total) AS total_revenue,
    AVG(order_total) AS avg_order_value,
    COUNT(DISTINCT user_id) AS unique_customers
FROM orders
WHERE status = 'completed'
GROUP BY DATE_TRUNC('month', order_date);

-- Create an index on the materialized view for faster queries
CREATE INDEX idx_monthly_sales_month 
ON monthly_sales_summary(month);

-- Query the materialized view (fast!)
SELECT * FROM monthly_sales_summary 
WHERE month >= '2024-01-01'
ORDER BY month DESC;

-- Refresh the data (blocking operation)
REFRESH MATERIALIZED VIEW monthly_sales_summary;

-- Refresh without blocking concurrent reads (PostgreSQL 9.4+)
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales_summary;

The CONCURRENTLY option requires a unique index on the materialized view but allows queries to continue accessing the old data while the refresh happens. This is essential for production systems where you can’t afford downtime.

Consider setting up automated refresh schedules using cron jobs or pg_cron:

-- Using pg_cron extension (if installed)
SELECT cron.schedule('refresh-sales-summary', '0 2 * * *', 
    'REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales_summary');

Advanced View Techniques

Views excel at implementing column-level security by selectively exposing data based on user roles:

-- Table with sensitive data
CREATE TABLE customer_payments (
    payment_id SERIAL PRIMARY KEY,
    user_id INTEGER,
    credit_card_number VARCHAR(16),
    amount DECIMAL(10,2),
    payment_date TIMESTAMP
);

-- View for customer service reps (masked card numbers)
CREATE VIEW customer_payments_masked AS
SELECT 
    payment_id,
    user_id,
    '****-****-****-' || RIGHT(credit_card_number, 4) AS credit_card_last4,
    amount,
    payment_date
FROM customer_payments;

-- Grant access only to the view
GRANT SELECT ON customer_payments_masked TO customer_service_role;
REVOKE ALL ON customer_payments FROM customer_service_role;

For recursive scenarios, PostgreSQL supports views that reference themselves:

-- Recursive view for organizational hierarchy
CREATE VIEW employee_hierarchy AS
WITH RECURSIVE org_chart AS (
    SELECT employee_id, manager_id, full_name, 1 AS level
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.manager_id, e.full_name, oc.level + 1
    FROM employees e
    JOIN org_chart oc ON e.manager_id = oc.employee_id
)
SELECT * FROM org_chart;

Best Practices and Performance Considerations

Naming conventions matter. Prefix views with v_ or use a suffix like _view to distinguish them from tables. This prevents confusion when reading queries and makes database exploration easier.

Avoid view chains. While you can create views based on other views, deep nesting degrades performance and makes debugging difficult. If you find yourself three views deep, reconsider your design.

Document your views. Use PostgreSQL’s COMMENT command to explain the view’s purpose:

COMMENT ON VIEW active_customers IS 
'Aggregates order data for active users. Refreshed in real-time. Used by marketing dashboard and customer service tools.';

Monitor query plans. Views don’t automatically improve performance—they just organize your queries. Always check execution plans:

EXPLAIN ANALYZE SELECT * FROM active_customers WHERE lifetime_value > 5000;

Consider view performance impact. Complex views with multiple joins can actually hurt performance if they’re used in further joins. In these cases, materialized views or denormalized tables might be better choices.

Use views for API stability. When you need to change table structures, views can maintain backward compatibility. Your application continues querying the view while the underlying schema evolves.

Views are powerful tools for managing complexity in PostgreSQL databases. They simplify application code, enhance security, and provide flexibility in how you present data. Start with simple views for common queries, graduate to materialized views for reporting needs, and leverage updatable views when appropriate. Used thoughtfully, views become an essential part of your database architecture.

Liked this? There's more.

Every week: one practical technique, explained simply, with code you can use immediately.