PostgreSQL Error Handling: EXCEPTION Blocks & RAISE

Functions fail sometimes - queries return no data, constraints are violated, division by zero occurs. Good error handling prevents crashes and provides meaningful error messages.

This guide covers raising errors, catching exceptions, and implementing robust error handling in PL/pgSQL.

What You’ll Learn

This guide covers:

  • RAISE for errors, warnings, and notices
  • EXCEPTION blocks for error catching
  • Specific exception types
  • SQLERRM and error messages
  • Best practices for error messages
  • Common exception patterns
  • Partial rollback with subtransactions
  • Logging errors

RAISE: Signal Errors and Warnings

RAISE EXCEPTION: Stop Execution

CREATE FUNCTION require_positive_salary(salary DECIMAL)
RETURNS VARCHAR AS $$
BEGIN
    IF salary <= 0 THEN
        RAISE EXCEPTION 'Salary must be positive';
    END IF;

    RETURN 'Salary is valid';
END;
$$ LANGUAGE plpgsql;

-- Call with valid salary
SELECT require_positive_salary(75000);  -- Returns 'Salary is valid'

-- Call with invalid salary
SELECT require_positive_salary(-1000);  -- ERROR: Salary must be positive

RAISE EXCEPTION stops the function immediately and rolls back any changes.

RAISE with Error Code

CREATE FUNCTION safe_insert_employee(
    first_name VARCHAR,
    last_name VARCHAR,
    salary DECIMAL
)
RETURNS INTEGER AS $$
DECLARE
    v_emp_id INTEGER;
BEGIN
    -- Validate inputs
    IF first_name IS NULL OR first_name = '' THEN
        RAISE EXCEPTION 'INVALID_INPUT'
            USING DETAIL = 'First name is required',
                  HINT = 'Please provide a first name';
    END IF;

    IF salary < 0 THEN
        RAISE EXCEPTION 'INVALID_SALARY'
            USING DETAIL = 'Salary cannot be negative',
                  HINT = 'Enter a positive salary amount';
    END IF;

    INSERT INTO employees (first_name, last_name, salary, dept_id, hire_date)
    VALUES (first_name, last_name, salary, 1, CURRENT_DATE)
    RETURNING emp_id INTO v_emp_id;

    RETURN v_emp_id;
EXCEPTION
    WHEN UNIQUE_VIOLATION THEN
        RAISE EXCEPTION 'DUPLICATE_EMPLOYEE'
            USING DETAIL = 'An employee with this name already exists';
END;
$$ LANGUAGE plpgsql;

Error codes and HINT/DETAIL messages help users understand what went wrong.

RAISE NOTICE, WARNING

CREATE FUNCTION warn_high_salary(salary DECIMAL)
RETURNS VARCHAR AS $$
BEGIN
    RAISE NOTICE 'Processing salary: %', salary;  -- Debug info

    IF salary > 200000 THEN
        RAISE WARNING 'Salary exceeds $200,000';  -- Warning (continues)
    END IF;

    IF salary > 500000 THEN
        RAISE EXCEPTION 'Salary exceeds company maximum of $500,000';  -- Error (stops)
    END IF;

    RETURN 'Processed';
END;
$$ LANGUAGE plpgsql;

SELECT warn_high_salary(150000);     -- Returns 'Processed'
SELECT warn_high_salary(250000);     -- Warning, returns 'Processed'
SELECT warn_high_salary(600000);     -- ERROR
  • NOTICE: Informational message (lowest severity)
  • WARNING: Warning message (medium severity)
  • EXCEPTION: Error message (stops execution)

RAISE with String Formatting

CREATE FUNCTION formatted_error(emp_id INTEGER, reason VARCHAR)
RETURNS VARCHAR AS $$
DECLARE
    v_first_name VARCHAR;
BEGIN
    SELECT first_name INTO v_first_name
    FROM employees
    WHERE emp_id = formatted_error.emp_id;

    IF v_first_name IS NULL THEN
        RAISE EXCEPTION 'Employee % not found: %', emp_id, reason;
    END IF;

    RETURN 'Employee found: ' || v_first_name;
END;
$$ LANGUAGE plpgsql;

SELECT formatted_error(9999, 'Attempting salary update');
-- ERROR: Employee 9999 not found: Attempting salary update

Use % placeholders for string formatting in RAISE.

EXCEPTION Blocks

Catching Specific Exceptions

CREATE FUNCTION safe_update_salary(emp_id INTEGER, new_salary DECIMAL)
RETURNS VARCHAR AS $$
BEGIN
    UPDATE employees
    SET salary = new_salary
    WHERE emp_id = safe_update_salary.emp_id;

    RETURN 'Salary updated successfully';

EXCEPTION
    WHEN NUMERIC_VALUE_OUT_OF_RANGE THEN
        RETURN 'Error: Salary value is out of range';

    WHEN FOREIGN_KEY_VIOLATION THEN
        RETURN 'Error: Employee ID does not exist';

    WHEN CHECK_VIOLATION THEN
        RETURN 'Error: Salary violates constraint';

    WHEN OTHERS THEN
        RETURN 'Error: Unexpected error occurred';
END;
$$ LANGUAGE plpgsql;

SELECT safe_update_salary(1, 95000);    -- Success
SELECT safe_update_salary(9999, 95000); -- FOREIGN_KEY_VIOLATION

Catch specific exceptions before using OTHERS.

Getting Error Information

CREATE FUNCTION detailed_error_info(emp_id INTEGER)
RETURNS TABLE(success BOOLEAN, error_code VARCHAR, error_message TEXT) AS $$
DECLARE
    v_result RECORD;
BEGIN
    BEGIN
        SELECT * INTO v_result FROM employees WHERE emp_id = detailed_error_info.emp_id;
        RETURN QUERY SELECT true::BOOLEAN, ''::VARCHAR, ''::TEXT;
    EXCEPTION
        WHEN SQLSTATE '23503' THEN  -- Foreign key violation
            RETURN QUERY SELECT false, 'FK_VIOLATION', SQLERRM;
        WHEN SQLSTATE '23505' THEN  -- Unique violation
            RETURN QUERY SELECT false, 'UNIQUE_VIOLATION', SQLERRM;
        WHEN OTHERS THEN
            RETURN QUERY SELECT false, SQLSTATE, SQLERRM;
    END;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM detailed_error_info(1);
  • SQLSTATE: Error code (5-character string)
  • SQLERRM: Error message text
  • Exception name: Human-readable exception (UNIQUE_VIOLATION, FOREIGN_KEY_VIOLATION, etc.)

Exception with Condition Code

CREATE FUNCTION check_employee_exists(emp_id INTEGER)
RETURNS VARCHAR AS $$
DECLARE
    v_found BOOLEAN;
BEGIN
    SELECT EXISTS(SELECT 1 FROM employees WHERE emp_id = check_employee_exists.emp_id)
    INTO v_found;

    IF NOT v_found THEN
        RAISE EXCEPTION SQLSTATE '22000' USING MESSAGE = 'Employee not found';
    END IF;

    RETURN 'Employee exists';

EXCEPTION
    WHEN SQLSTATE '22000' THEN
        RETURN 'Error: ' || SQLERRM;
    WHEN OTHERS THEN
        RETURN 'Unexpected error: ' || SQLERRM;
END;
$$ LANGUAGE plpgsql;

Use SQLSTATE for standard error codes.

Common Exception Types

Most Used Exceptions

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        -- SELECT returned no rows (only in code using INTO)
        RAISE EXCEPTION 'Record not found';

    WHEN TOO_MANY_ROWS THEN
        -- SELECT INTO returned multiple rows
        RAISE EXCEPTION 'Expected single row, got multiple';

    WHEN UNIQUE_VIOLATION THEN
        -- Unique constraint violated
        RAISE EXCEPTION 'Duplicate record';

    WHEN FOREIGN_KEY_VIOLATION THEN
        -- Foreign key constraint violated
        RAISE EXCEPTION 'Referenced record does not exist';

    WHEN CHECK_VIOLATION THEN
        -- Check constraint violated
        RAISE EXCEPTION 'Data fails validation';

    WHEN NOT_NULL_VIOLATION THEN
        -- NOT NULL constraint violated
        RAISE EXCEPTION 'Required field is null';

    WHEN DIVISION_BY_ZERO THEN
        -- Division by zero
        RAISE EXCEPTION 'Cannot divide by zero';

    WHEN NUMERIC_VALUE_OUT_OF_RANGE THEN
        -- Number too large for type
        RAISE EXCEPTION 'Value out of range';

    WHEN STRING_DATA_RIGHT_TRUNCATION THEN
        -- String too long for column
        RAISE EXCEPTION 'Text exceeds maximum length';

    WHEN OTHERS THEN
        -- Catch all remaining errors
        RAISE EXCEPTION 'Unexpected error: %', SQLERRM;
END;

Real-World Error Handling Patterns

Validate Input Before Processing

CREATE FUNCTION calculate_bonus(emp_id INTEGER, bonus_pct DECIMAL)
RETURNS DECIMAL AS $$
DECLARE
    v_salary DECIMAL;
BEGIN
    -- Validate inputs
    IF emp_id IS NULL THEN
        RAISE EXCEPTION 'Employee ID cannot be NULL';
    END IF;

    IF bonus_pct IS NULL OR bonus_pct < 0 OR bonus_pct > 100 THEN
        RAISE EXCEPTION 'Bonus percentage must be between 0 and 100, got %', bonus_pct;
    END IF;

    -- Get salary
    SELECT salary INTO v_salary
    FROM employees
    WHERE emp_id = calculate_bonus.emp_id;

    IF v_salary IS NULL THEN
        RAISE EXCEPTION 'Employee % not found', emp_id;
    END IF;

    RETURN v_salary * (bonus_pct / 100);
END;
$$ LANGUAGE plpgsql;

SELECT calculate_bonus(1, 10);      -- Success
SELECT calculate_bonus(NULL, 10);   -- ERROR: Employee ID cannot be NULL
SELECT calculate_bonus(1, 150);     -- ERROR: Bonus percentage must be between 0 and 100

Graceful Degradation

CREATE FUNCTION get_employee_with_fallback(emp_id INTEGER)
RETURNS TABLE(id INTEGER, name VARCHAR, salary DECIMAL) AS $$
BEGIN
    BEGIN
        RETURN QUERY
        SELECT emp_id, first_name || ' ' || last_name, salary
        FROM employees
        WHERE emp_id = get_employee_with_fallback.emp_id;

        -- If no results, RAISE NO_DATA_FOUND
        IF NOT FOUND THEN
            RAISE NO_DATA_FOUND;
        END IF;

    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            -- Return default/empty result
            RETURN QUERY SELECT NULL::INTEGER, 'Not Found', 0::DECIMAL;
    END;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM get_employee_with_fallback(9999);

Result:

id | name      | salary
----|-----------|--------
    | Not Found | 0

Subtransactions for Partial Rollback

CREATE FUNCTION process_batch_updates(emp_ids INTEGER[])
RETURNS TABLE(emp_id INTEGER, status VARCHAR) AS $$
DECLARE
    v_emp_id INTEGER;
    v_status VARCHAR;
BEGIN
    FOREACH v_emp_id IN ARRAY emp_ids LOOP
        BEGIN
            -- Try to update each employee
            UPDATE employees
            SET salary = salary * 1.05
            WHERE emp_id = v_emp_id;

            v_status := 'Success';

        EXCEPTION
            WHEN CHECK_VIOLATION THEN
                -- Salary constraint failed, but continue processing other employees
                v_status := 'Failed: Salary constraint violated';
            WHEN OTHERS THEN
                v_status := 'Failed: ' || SQLERRM;
        END;

        RETURN NEXT (v_emp_id, v_status);
    END LOOP;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM process_batch_updates(ARRAY[1, 2, 3, 9999]);

Result:

emp_id | status
-------+-------------------------------------
     1 | Success
     2 | Success
     3 | Success
  9999 | Failed: 0 rows updated

Individual failures don’t stop batch processing.

Logging Errors to Audit Table

CREATE TABLE error_log (
    log_id SERIAL PRIMARY KEY,
    error_time TIMESTAMP DEFAULT NOW(),
    error_type VARCHAR(50),
    error_message TEXT,
    error_code VARCHAR(5),
    context_info TEXT
);

CREATE FUNCTION safe_operation_with_logging(operation VARCHAR)
RETURNS VARCHAR AS $$
BEGIN
    -- Perform operation
    IF operation = 'bad' THEN
        RAISE EXCEPTION 'Bad operation';
    END IF;

    RETURN 'Success';

EXCEPTION
    WHEN OTHERS THEN
        -- Log error
        INSERT INTO error_log (error_type, error_message, error_code, context_info)
        VALUES (
            'OPERATION_ERROR',
            SQLERRM,
            SQLSTATE,
            'Operation: ' || operation
        );

        -- Re-raise or return error message
        RAISE EXCEPTION 'Operation failed. Error logged as %.', SQLSTATE;
END;
$$ LANGUAGE plpgsql;

SELECT safe_operation_with_logging('bad');

SELECT * FROM error_log;

Result:

log_id | error_time          | error_type        | error_message      | error_code | context_info
------+---------------------+-------------------+-------------------+----------+------------------
     1 | 2026-02-21 10:45:00 | OPERATION_ERROR   | Bad operation     | P0001    | Operation: bad

Best Practices for Error Messages

Clear, Actionable Messages

-- Bad error message
RAISE EXCEPTION 'Invalid input';

-- Good error message
RAISE EXCEPTION 'Salary must be positive. Provided value: %. Please enter a salary >= 0.', new_salary;

-- Better: Specific, actionable
RAISE EXCEPTION USING
    MESSAGE = 'Invalid salary for employee',
    DETAIL = 'Provided salary: %s (negative values not allowed)',
    HINT = 'Enter a positive decimal value',
    ERRCODE = 'invalid_parameter_value';

Include:

  • What went wrong
  • What value caused the problem
  • What’s the valid range
  • How to fix it

Contextual Information

CREATE FUNCTION update_salary_with_context(emp_id INTEGER, new_salary DECIMAL)
RETURNS VARCHAR AS $$
DECLARE
    v_current_salary DECIMAL;
    v_emp_name VARCHAR;
BEGIN
    SELECT first_name || ' ' || last_name, salary
    INTO v_emp_name, v_current_salary
    FROM employees
    WHERE emp_id = update_salary_with_context.emp_id;

    IF v_emp_name IS NULL THEN
        RAISE EXCEPTION 'Employee % not found', emp_id;
    END IF;

    IF new_salary > v_current_salary * 2 THEN
        RAISE EXCEPTION 'Salary increase too large for % (current: $%, new: $%, increase: %)',
            v_emp_name, v_current_salary, new_salary,
            ROUND(((new_salary - v_current_salary) / v_current_salary * 100)::NUMERIC, 2) || '%'
            USING HINT = 'Increases over 100% require manager approval';
    END IF;

    UPDATE employees SET salary = new_salary WHERE emp_id = update_salary_with_context.emp_id;
    RETURN 'Updated ' || v_emp_name || ' salary to $' || new_salary;
END;
$$ LANGUAGE plpgsql;

Common Errors & Solutions

Error: “EXCEPTION without OTHERS at end of EXCEPTION block”

Problem:

CREATE FUNCTION bad_error_handling() AS $$
BEGIN
    RAISE EXCEPTION 'Test';
EXCEPTION
    WHEN UNIQUE_VIOLATION THEN
        RAISE EXCEPTION 'Duplicate';
    -- Missing WHEN OTHERS or final error handler
END;
$$ LANGUAGE plpgsql;

Solution:

Always include WHEN OTHERS:

EXCEPTION
    WHEN UNIQUE_VIOLATION THEN
        RAISE EXCEPTION 'Duplicate';
    WHEN OTHERS THEN
        RAISE EXCEPTION 'Unexpected error: %', SQLERRM;
END;

Error: “SQLERRM outside EXCEPTION”

Problem:

BEGIN
    RETURN SQLERRM;  -- Can't use outside EXCEPTION block
END;

Solution:

Use SQLERRM only in EXCEPTION blocks:

EXCEPTION
    WHEN OTHERS THEN
        RETURN 'Error: ' || SQLERRM;
END;

Silent Failures

Problem:

CREATE FUNCTION silent_failure() AS $$
BEGIN
    INSERT INTO table (col) VALUES (invalid);
    -- Fails silently, function continues
END;
$$ LANGUAGE plpgsql;

Solution:

Check results or handle exceptions:

CREATE FUNCTION explicit_error_handling() AS $$
DECLARE
    v_affected_rows INTEGER;
BEGIN
    INSERT INTO table (col) VALUES (valid);
    GET DIAGNOSTICS v_affected_rows = ROW_COUNT;

    IF v_affected_rows = 0 THEN
        RAISE EXCEPTION 'Insert failed: no rows affected';
    END IF;
EXCEPTION
    WHEN OTHERS THEN
        RAISE EXCEPTION 'Insert failed: %', SQLERRM;
END;
$$ LANGUAGE plpgsql;

Performance Tips

1. Minimize Try/Catch Overhead

-- Slow: Every call tries to handle exception
CREATE FUNCTION slow_check(value INTEGER) RETURNS BOOLEAN AS $$
BEGIN
    BEGIN
        PERFORM 1 / value;  -- Expect division by zero
        RETURN TRUE;
    EXCEPTION
        WHEN DIVISION_BY_ZERO THEN RETURN FALSE;
    END;
END;
$$ LANGUAGE plpgsql;

-- Better: Validate upfront
CREATE FUNCTION fast_check(value INTEGER) RETURNS BOOLEAN AS $$
BEGIN
    RETURN value != 0;
END;
$$ LANGUAGE plpgsql;

2. Exception Handling is Expensive

-- Don't use exceptions for control flow
CREATE FUNCTION bad_pattern(value INTEGER) RETURNS BOOLEAN AS $$
BEGIN
    BEGIN
        PERFORM 1 / value;  -- Expensive!
        RETURN TRUE;
    EXCEPTION
        WHEN DIVISION_BY_ZERO THEN RETURN FALSE;
    END;
END;
$$ LANGUAGE plpgsql;

-- Check conditions instead
CREATE FUNCTION good_pattern(value INTEGER) RETURNS BOOLEAN AS $$
BEGIN
    RETURN value != 0;
END;
$$ LANGUAGE plpgsql;

3. Log Selectively

-- Log only critical errors
IF severity_level = 'CRITICAL' THEN
    INSERT INTO error_log (...) VALUES (...);
END IF;

FAQ

Q: When should I use RAISE vs EXCEPTION block? A: RAISE to signal errors. EXCEPTION blocks to catch and handle them.

Q: Can I catch the same exception twice? A: No, once an exception is caught, it stops. Use WHEN OTHERS to catch remaining errors.

Q: How do I re-raise an exception? A: Just use RAISE without arguments inside EXCEPTION block to re-raise.

Q: Should I always use WHEN OTHERS? A: Yes, it’s a safety net. Always include it at the end of EXCEPTION blocks.

Q: Can I nest EXCEPTION blocks? A: Yes, each BEGIN/END block can have its own EXCEPTION handler.

Complete your PostgreSQL programming expertise:

Robust error handling separates production-ready code from examples. Master these patterns and your database code will be bulletproof.