Kenhuang Academy
  • English

      • PostgreSQL Views

      Overview

      Views are virtual tables based on the result of a SQL query. Views do not store data themselves; instead, they dynamically execute their defined SQL statement each time they are queried. The main purposes of views include simplifying complex queries, providing data access security control, and offering a stable data interface for applications.

      Creating Views

      Use the CREATE VIEW statement to create a view:

      -- Create simple view: filter active users
CREATE VIEW active_users AS
SELECT * FROM users WHERE status = 'active';

-- Create complex view: user spending summary
CREATE VIEW user_summary AS
SELECT 
    u.id,
    u.name,
    u.email,
    COUNT(o.id) AS order_count,
    COALESCE(SUM(o.total), 0) AS total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id;

      Use CREATE OR REPLACE VIEW to update a view's definition if it already exists:

      -- Create or replace view
CREATE OR REPLACE VIEW active_users AS
SELECT id, name, email, created_at
FROM users
WHERE status = 'active';

      Using Views

      Views are used exactly like regular tables:

      -- Query view
SELECT * FROM active_users;

-- Add filter conditions on view
SELECT * FROM user_summary WHERE total_spent > 1000;

-- Views can be joined with other tables
SELECT 
    a.name,
    a.email,
    p.product_name
FROM active_users a
JOIN orders o ON a.id = o.user_id
JOIN products p ON o.product_id = p.id;

      Updatable Views

      Simple views that meet the following conditions support INSERT, UPDATE, and DELETE operations:

      • View is based on a single table
      • Does not contain aggregate functions, DISTINCT, GROUP BY, HAVING, UNION
      • Does not contain subqueries
      -- Create updatable view
CREATE VIEW simple_users AS
SELECT id, name, email FROM users;

-- Insert data through view
INSERT INTO simple_users (id, name, email)
VALUES (1, 'John', 'john@example.com');

-- Update data through view
UPDATE simple_users SET name = 'Jane' WHERE id = 1;

-- Delete data through view
DELETE FROM simple_users WHERE id = 1;

      WITH CHECK OPTION

      Use WITH CHECK OPTION to prevent inserting or updating data through a view that doesn't meet the view's conditions:

      -- Create view with check option
CREATE VIEW active_users AS
SELECT * FROM users WHERE status = 'active'
WITH CHECK OPTION;

-- The following operation will fail because inserted data doesn't satisfy status = 'active'
INSERT INTO active_users (id, name, status)
VALUES (2, 'Bob', 'inactive');  -- Error!

      Materialized Views

      Materialized views differ from regular views by actually storing query results on disk. They are suitable for computationally intensive scenarios where data updates are infrequent:

      -- Create materialized view: monthly sales statistics
CREATE MATERIALIZED VIEW monthly_sales AS
SELECT 
    DATE_TRUNC('month', created_at) AS month,
    COUNT(*) AS order_count,
    SUM(total) AS revenue
FROM orders
GROUP BY DATE_TRUNC('month', created_at);

-- Refresh materialized view (full refresh, locks table)
REFRESH MATERIALIZED VIEW monthly_sales;

-- Concurrent refresh (doesn't lock queries, requires unique index)
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales;
      Tip

      When using the CONCURRENTLY option for concurrent refresh, the materialized view must have a unique index.

      Dropping Views

      -- Drop view
DROP VIEW active_users;

-- Drop view if exists
DROP VIEW IF EXISTS old_view;

-- Cascade drop (also drops dependent objects)
DROP VIEW user_summary CASCADE;

-- Drop materialized view
DROP MATERIALIZED VIEW monthly_sales;

      Summary

      View key points:

      • Views are virtual tables based on query definitions and do not actually store data
      • Simplify complex queries and provide data security control
      • Simple views can perform insert, update, and delete operations
      • WITH CHECK OPTION ensures data modified through views always meets view conditions
      • Materialized views cache results to disk, suitable for computation-intensive queries, require manual refresh