PostgreSQL Error Handling

Key Error Handling Concepts

EXCEPTION WHEN ... THEN

division_by_zero, unique_violation

RAISE EXCEPTION 'message'

SQLERRM, SQLSTATE

🔹 Basic Exception Handling

The EXCEPTION block catches errors that occur in the BEGIN section. When an error happens, control jumps to the matching WHEN clause, allowing you to handle the error gracefully.

-- Create sample table
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) UNIQUE,
    price DECIMAL(10,2)
);

-- Function with basic error handling
CREATE OR REPLACE FUNCTION add_product(
    p_name VARCHAR,
    p_price DECIMAL
)
RETURNS TEXT
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO products (name, price) 
    VALUES (p_name, p_price);
    
    RETURN 'Product added successfully';
EXCEPTION
    WHEN unique_violation THEN
        RETURN 'Error: Product name already exists';
    WHEN OTHERS THEN
        RETURN 'Error: Could not add product';
END;
$$;

-- Test the function
SELECT add_product('Laptop', 999.99);
SELECT add_product('Laptop', 899.99);  -- Duplicate name

add_product
--------------------------------
Product added successfully

add_product
--------------------------------
Error: Product name already exists

🔹 Common Error Conditions

PostgreSQL provides predefined error conditions for common database errors. Using specific error conditions allows you to handle different error types with appropriate responses instead of generic error messages.

-- Function handling multiple error types
CREATE OR REPLACE FUNCTION safe_divide(
    numerator DECIMAL,
    denominator DECIMAL
)
RETURNS DECIMAL
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN numerator / denominator;
EXCEPTION
    WHEN division_by_zero THEN
        RAISE NOTICE 'Cannot divide by zero';
        RETURN NULL;
    WHEN numeric_value_out_of_range THEN
        RAISE NOTICE 'Result is too large';
        RETURN NULL;
    WHEN OTHERS THEN
        RAISE NOTICE 'Unexpected error occurred';
        RETURN NULL;
END;
$$;

-- Test different error scenarios
SELECT safe_divide(100, 0);    -- Division by zero
SELECT safe_divide(100, 5);    -- Success

division_by_zero        - Divide by zero
unique_violation        - Duplicate key
foreign_key_violation   - FK constraint failed
not_null_violation      - NULL in NOT NULL column
check_violation         - CHECK constraint failed
numeric_value_out_of_range - Number too large/small

🔹 RAISE Statements

RAISE generates messages and errors at different severity levels. You can use it to provide feedback, warnings, or stop execution with custom error messages that help users understand what went wrong.

-- Function with RAISE statements
CREATE OR REPLACE FUNCTION update_price(
    p_id INTEGER,
    p_new_price DECIMAL
)
RETURNS VOID
LANGUAGE plpgsql
AS $$
BEGIN
    -- Validation with custom error
    IF p_new_price < 0 THEN
        RAISE EXCEPTION 'Price cannot be negative: %', p_new_price;
    END IF;
    
    IF p_new_price = 0 THEN
        RAISE WARNING 'Price is zero for product %', p_id;
    END IF;
    
    UPDATE products SET price = p_new_price WHERE id = p_id;
    
    IF NOT FOUND THEN
        RAISE NOTICE 'Product % not found', p_id;
    ELSE
        RAISE NOTICE 'Price updated successfully';
    END IF;
END;
$$;

-- Test with different scenarios
SELECT update_price(1, 899.99);   -- Success
SELECT update_price(1, 0);        -- Warning
SELECT update_price(999, 100);    -- Not found
SELECT update_price(1, -50);      -- Exception

DEBUG    - Debugging information
LOG      - Server log messages
INFO     - Informational messages
NOTICE   - User notifications (default)
WARNING  - Warning messages
EXCEPTION - Error, aborts transaction

🔹 Getting Error Information

PostgreSQL provides special variables to access detailed error information within exception handlers. These variables help you log errors, debug issues, and provide meaningful feedback to users about what went wrong.

-- Function that captures error details
CREATE OR REPLACE FUNCTION process_order(order_id INTEGER)
RETURNS TEXT
LANGUAGE plpgsql
AS $$
DECLARE
    error_message TEXT;
    error_detail TEXT;
    error_hint TEXT;
    error_code TEXT;
BEGIN
    -- Some operation that might fail
    UPDATE products SET price = price * 1.1 WHERE id = order_id;
    
    RETURN 'Order processed successfully';
EXCEPTION
    WHEN OTHERS THEN
        GET STACKED DIAGNOSTICS
            error_message = MESSAGE_TEXT,
            error_detail = PG_EXCEPTION_DETAIL,
            error_hint = PG_EXCEPTION_HINT,
            error_code = RETURNED_SQLSTATE;
        
        RAISE NOTICE 'Error Code: %', error_code;
        RAISE NOTICE 'Error Message: %', error_message;
        RAISE NOTICE 'Error Detail: %', error_detail;
        
        RETURN 'Error processing order';
END;
$$;

SQLERRM              - Error message text
SQLSTATE             - 5-character error code
MESSAGE_TEXT         - Primary error message
PG_EXCEPTION_DETAIL  - Detailed error information
PG_EXCEPTION_HINT    - Hint for fixing error
PG_EXCEPTION_CONTEXT - Call stack trace

🔹 Nested Exception Handling

You can nest BEGIN-EXCEPTION blocks to handle errors at different levels. Inner blocks can catch specific errors while outer blocks handle broader error categories, providing layered error management.

-- Function with nested exception handling
CREATE OR REPLACE FUNCTION complex_operation(p_id INTEGER)
RETURNS TEXT
LANGUAGE plpgsql
AS $$
DECLARE
    result TEXT;
BEGIN
    -- Outer block
    BEGIN
        -- Inner block with specific error handling
        BEGIN
            UPDATE products SET price = price / 0 WHERE id = p_id;
        EXCEPTION
            WHEN division_by_zero THEN
                RAISE NOTICE 'Inner: Caught division by zero';
                RETURN 'Handled division error';
        END;
        
        -- This won't execute if inner block returns
        RETURN 'Operation completed';
    EXCEPTION
        WHEN OTHERS THEN
            RAISE NOTICE 'Outer: Caught unexpected error';
            RETURN 'Handled unexpected error';
    END;
END;
$$;

🔹 Transaction Error Handling

When errors occur in transactions, you can use exception handling to rollback changes and maintain data consistency. This ensures that partial updates don't leave your database in an inconsistent state.

-- Procedure with transaction error handling
CREATE OR REPLACE PROCEDURE transfer_funds(
    from_account INTEGER,
    to_account INTEGER,
    amount DECIMAL
)
LANGUAGE plpgsql
AS $$
BEGIN
    BEGIN
        -- Start transaction operations
        UPDATE accounts 
        SET balance = balance - amount 
        WHERE id = from_account;
        
        -- Simulate error check
        IF amount > 10000 THEN
            RAISE EXCEPTION 'Transfer amount exceeds limit';
        END IF;
        
        UPDATE accounts 
        SET balance = balance + amount 
        WHERE id = to_account;
        
        RAISE NOTICE 'Transfer completed successfully';
    EXCEPTION
        WHEN OTHERS THEN
            RAISE NOTICE 'Transfer failed: %', SQLERRM;
            RAISE EXCEPTION 'Transaction rolled back';
    END;
END;
$$;

🔹 Error Handling Best Practices

Effective error handling improves application reliability and user experience. Follow these practices to create robust database functions that handle errors gracefully and provide clear feedback when problems occur.