PostgreSQL Views

Key View Concepts

SELECT * FROM view_name

-- Expose only safe data

-- Use view multiple times

-- Hide JOIN complexity

🔹 Creating Simple Views

Views act as saved queries that you can reference like regular tables. They're perfect for simplifying repetitive queries and providing consistent data access patterns across your application.

-- Create sample tables
CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    country VARCHAR(50)
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    amount DECIMAL(10,2),
    order_date DATE
);

-- Insert sample data
INSERT INTO customers (name, email, country) VALUES
    ('John Doe', '[email protected]', 'USA'),
    ('Jane Smith', '[email protected]', 'UK');

INSERT INTO orders (customer_id, amount, order_date) VALUES
    (1, 150.00, '2024-01-15'),
    (1, 200.00, '2024-02-20'),
    (2, 300.00, '2024-01-10');

-- Create a simple view
CREATE VIEW customer_orders AS
SELECT 
    c.name,
    c.email,
    o.amount,
    o.order_date
FROM customers c
JOIN orders o ON c.id = o.customer_id;

-- Query the view
SELECT * FROM customer_orders;

name       | email              | amount  | order_date
-----------+--------------------+---------+------------
John Doe   | [email protected]   | 150.00  | 2024-01-15
John Doe   | [email protected]   | 200.00  | 2024-02-20
Jane Smith | [email protected]   | 300.00  | 2024-01-10

🔹 Views with Aggregations

Views can contain aggregate functions and GROUP BY clauses to create summary reports. These views provide pre-calculated statistics that are always up-to-date when queried.

-- Create view with aggregations
CREATE VIEW customer_summary AS
SELECT 
    c.id,
    c.name,
    COUNT(o.id) AS total_orders,
    SUM(o.amount) AS total_spent,
    AVG(o.amount) AS avg_order_value
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name;

-- Query the summary view
SELECT * FROM customer_summary
ORDER BY total_spent DESC;

id | name       | total_orders | total_spent | avg_order_value
---+------------+--------------+-------------+-----------------
 1 | John Doe   | 2            | 350.00      | 175.00
 2 | Jane Smith | 1            | 300.00      | 300.00

🔹 Updatable Views

Simple views that select from a single table without aggregations can be updated directly. PostgreSQL automatically translates INSERT, UPDATE, and DELETE operations on the view to the underlying table.

-- Create updatable view
CREATE VIEW usa_customers AS
SELECT id, name, email
FROM customers
WHERE country = 'USA';

-- Insert through view
INSERT INTO usa_customers (name, email)
VALUES ('Bob Wilson', '[email protected]');

-- Update through view
UPDATE usa_customers
SET email = '[email protected]'
WHERE name = 'John Doe';

-- Delete through view
DELETE FROM usa_customers
WHERE name = 'Bob Wilson';

Views are updatable if they:
- Select from a single table
- Don't use DISTINCT, GROUP BY, HAVING
- Don't use aggregate functions
- Don't use UNION, INTERSECT, EXCEPT

🔹 Materialized Views

Materialized views physically store query results, unlike regular views. They improve performance for expensive queries but require manual refreshing to update the stored data when underlying tables change.

-- Create materialized view
CREATE MATERIALIZED VIEW monthly_sales AS
SELECT 
    DATE_TRUNC('month', order_date) AS month,
    COUNT(*) AS order_count,
    SUM(amount) AS total_sales
FROM orders
GROUP BY DATE_TRUNC('month', order_date);

-- Query materialized view (fast!)
SELECT * FROM monthly_sales;

-- Refresh materialized view
REFRESH MATERIALIZED VIEW monthly_sales;

-- Refresh without locking (concurrent)
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales;

month       | order_count | total_sales
------------+-------------+-------------
2024-01-01  | 2           | 450.00
2024-02-01  | 1           | 200.00

🔹 Managing Views

PostgreSQL provides commands to modify, replace, and remove views. You can also query system catalogs to see all views and their definitions in your database.

-- Replace existing view
CREATE OR REPLACE VIEW customer_orders AS
SELECT 
    c.name,
    c.country,
    o.amount,
    o.order_date
FROM customers c
JOIN orders o ON c.id = o.customer_id;

-- Rename view
ALTER VIEW customer_orders RENAME TO order_details;

-- Drop view
DROP VIEW IF EXISTS order_details;

-- Drop materialized view
DROP MATERIALIZED VIEW IF EXISTS monthly_sales;

-- View all views in database
SELECT table_name, view_definition
FROM information_schema.views
WHERE table_schema = 'public';

🔹 View Benefits

Views provide multiple advantages for database design and application development. They help organize complex queries, improve security, and maintain consistent data access patterns across your applications.