EXCEPTION Handler

Catches and handles errors within a block.

Syntax

EXCEPTION
  WHEN <condition> THEN <statements>
  [WHEN <condition> THEN <statements>]
  ...

Conditions: SQLSTATE 'code', <named_condition>, OTHERS

Description

## Overview The EXCEPTION section of a BEGIN...END block catches runtime errors and routes them to handler clauses. Each WHEN clause specifies one or more conditions to match against the error, and the first matching handler executes. ## Behavior - When an error occurs in the block body, the executor iterates through WHEN clauses in declaration order. The first clause whose condition matches the error executes its handler body. - Condition matching supports three forms: SQLSTATE 'code' matches a specific 5-character SQL state, named conditions (e.g., unique_violation, division_by_zero) match by condition name, and OTHERS matches any error. - The exception matching function converts errors to ExceptionContext objects containing sqlstate, message, detail, hint, and context information. Named conditions are mapped to their standard SQLSTATE codes for comparison. - Inside a handler, SQLERRM and SQLSTATE are available as implicit variables containing the error message and SQL state code respectively. - GET STACKED DIAGNOSTICS can retrieve additional exception details (RETURNED_SQLSTATE, MESSAGE_TEXT, EXCEPTION_DETAIL, EXCEPTION_HINT, EXCEPTION_CONTEXT, COLUMN_NAME, TABLE_NAME, etc.). - After a handler completes, control continues after the END of the block that contains the EXCEPTION section. - If no handler matches, the error propagates to the enclosing block. ## Differences from PostgreSQL - PostgreSQL creates an implicit savepoint before executing the block body, and rolls back to that savepoint when an exception is caught. DeltaForge does not use savepoints or subtransactions. Any DML executed before the error remains committed. - The set of recognized named conditions may differ from PostgreSQL's complete catalog. Use SQLSTATE codes for precise matching when a named condition is not recognized. - Re-raising the original exception (bare `RAISE;` inside a handler) follows the same semantics as PostgreSQL.

Examples

DO $$
BEGIN
  INSERT INTO unique_table (id) VALUES (1);
EXCEPTION
  WHEN unique_violation THEN
    PRINT 'Duplicate key, skipping insert';
END;
$$;

DO $$
DECLARE
  v_state TEXT;
  v_msg TEXT;
BEGIN
  SELECT 1/0;
EXCEPTION
  WHEN division_by_zero THEN
    GET STACKED DIAGNOSTICS v_state = RETURNED_SQLSTATE, v_msg = MESSAGE_TEXT;
    PRINT 'Caught:', v_state, v_msg;
END;
$$;

DO $$
BEGIN
  BEGIN
    EXECUTE 'SELECT * FROM nonexistent_table';
  EXCEPTION
    WHEN OTHERS THEN
      PRINT 'Error: ' || SQLERRM;
      PRINT 'State: ' || SQLSTATE;
  END;
  PRINT 'Execution continued after error';
END;
$$;

DO $$
DECLARE
  rec RECORD;
BEGIN
  FOR rec IN SELECT id, table_name FROM migration_tasks LOOP
    BEGIN
      EXECUTE 'ALTER TABLE ' || rec.table_name || ' ADD COLUMN migrated BOOLEAN DEFAULT false';
      PRINT 'Migrated:', rec.table_name;
    EXCEPTION
      WHEN duplicate_column THEN
        PRINT 'Column already exists on:', rec.table_name;
      WHEN OTHERS THEN
        RAISE WARNING 'Failed to migrate %: %', rec.table_name, SQLERRM;
    END;
  END LOOP;
END;
$$;

DO $$
BEGIN
  RAISE EXCEPTION 'Custom error'
    USING DETAIL = 'Additional details here',
          HINT = 'Check the input parameters';
EXCEPTION
  WHEN OTHERS THEN
    DECLARE
      v_detail TEXT;
      v_hint TEXT;
    BEGIN
      GET STACKED DIAGNOSTICS v_detail = EXCEPTION_DETAIL, v_hint = EXCEPTION_HINT;
      PRINT 'Detail:', v_detail;
      PRINT 'Hint:', v_hint;
    END;
END;
$$;

Pitfalls

• DML executed before the error is not rolled back when a handler catches the exception. Design exception-safe blocks by performing checks before DML or using idempotent operations.
• WHEN OTHERS catches all errors, including unexpected system errors. Use specific conditions when possible and reserve OTHERS as a last resort with appropriate logging.
• Exception handlers add overhead because the executor must track exception context. Avoid using EXCEPTION blocks in tight loops when errors are expected to be rare.
• SQLERRM and SQLSTATE are only available inside an exception handler. Accessing them outside a handler returns NULL or raises an error.

See Also

• RAISE
• GET_DIAGNOSTICS
• BLOCK
• ASSERT_PLPGSQL