Exception Handling

When a runtime error occurs in PL/pgSQL, an exception is raised. The EXCEPTION block lets you catch errors, log them, and handle them gracefully rather than aborting the entire transaction.

The EXCEPTION Block

BEGIN
  -- risky statements
EXCEPTION
  WHEN exception_name THEN
    -- handle it
  WHEN OTHERS THEN
    -- catch anything else
END;

After the handler runs, the block ends — execution does not return to the statement that raised the error.

Important: Subtransactions

When an exception is caught, PostgreSQL rolls back all changes made since the start of the BEGIN block — not the entire transaction. Changes made before the block remain intact.

DO $$
BEGIN
  INSERT INTO employees(name) VALUES ('Alice');  -- kept
  BEGIN
    INSERT INTO employees(id, name) VALUES (1, 'Bob');  -- might violate PK
  EXCEPTION
    WHEN unique_violation THEN
      RAISE NOTICE 'Duplicate ID — skipped Bob';
  END;
  -- Alice's insert is still committed
END;
$$;

Common Exception Names

Exception	Raised when
no_data_found	SELECT INTO found no rows
too_many_rows	SELECT INTO returned more than one row
unique_violation	Unique or primary key constraint violated
foreign_key_violation	Foreign key constraint violated
not_null_violation	NULL inserted into a NOT NULL column
division_by_zero	Division by zero
numeric_value_out_of_range	Number too large for its type
undefined_table	Query references a table that doesn't exist
case_not_found	No WHEN matched in CASE and no ELSE

Exception names in PostgreSQL use snake_case (lowercase with underscores).

SQLSTATE and SQLERRM

Inside an exception handler, two special variables are available:

• SQLSTATE — the 5-character SQL state code (e.g. '23505' for unique_violation)
• SQLERRM — the human-readable error message

EXCEPTION
  WHEN OTHERS THEN
    RAISE NOTICE 'Error %: %', SQLSTATE, SQLERRM;

GET STACKED DIAGNOSTICS

For full error details, use GET STACKED DIAGNOSTICS:

EXCEPTION
  WHEN OTHERS THEN
    DECLARE
      v_state   TEXT;
      v_msg     TEXT;
      v_detail  TEXT;
      v_context TEXT;
    BEGIN
      GET STACKED DIAGNOSTICS
        v_state   = RETURNED_SQLSTATE,
        v_msg     = MESSAGE_TEXT,
        v_detail  = PG_EXCEPTION_DETAIL,
        v_context = PG_EXCEPTION_CONTEXT;
      RAISE NOTICE 'State: %, Message: %', v_state, v_msg;
    END;

RAISE EXCEPTION (Throwing Errors)

To signal an error from your own code:

IF p_salary < 0 THEN
  RAISE EXCEPTION 'Salary cannot be negative (got %)', p_salary
    USING ERRCODE = 'check_violation';
END IF;