Handling Errors and Exceptions
1. Understanding Error Codes
| SQLSTATE | Class |
|---|
| 23xxx | Integrity constraint |
| 22xxx | Data exception |
| 40001 / 40P01 | Serialization failure / deadlock |
| 42xxx | Syntax / access rule |
| 53xxx | Insufficient resources |
| P0001 | raise_exception (user RAISE) |
2. Using EXCEPTION Block in PL/pgSQL
BEGIN
...
EXCEPTION
WHEN unique_violation THEN ...
WHEN OTHERS THEN ...
END;
3. Catching Specific Errors
EXCEPTION
WHEN foreign_key_violation OR not_null_violation THEN
RAISE NOTICE 'data problem: %', SQLERRM;
4. Catching All Errors
EXCEPTION WHEN OTHERS THEN
RAISE NOTICE 'unexpected %: %', SQLSTATE, SQLERRM;
RAISE; -- re-raise
5. Using RAISE Statement
RAISE EXCEPTION 'bad input %', x USING ERRCODE = 'check_violation';
RAISE NOTICE 'name=%, age=%', n, a; -- % placeholders, positional
7. Using RAISE with HINT and DETAIL
RAISE EXCEPTION 'invalid currency %', c
USING DETAIL = 'must be ISO 4217 3-letter code',
HINT = 'try USD, EUR, GBP',
ERRCODE = '22023';
8. Using ASSERT Statement
ASSERT row_count > 0, 'no rows returned';
9. Using GET STACKED DIAGNOSTICS
EXCEPTION WHEN OTHERS THEN
GET STACKED DIAGNOSTICS
v_state = RETURNED_SQLSTATE,
v_msg = MESSAGE_TEXT,
v_detail = PG_EXCEPTION_DETAIL,
v_hint = PG_EXCEPTION_HINT,
v_context = PG_EXCEPTION_CONTEXT;
INSERT INTO errors VALUES(v_state, v_msg, v_detail, v_hint, v_context);
10. Using SQLERRM Variable
RAISE WARNING 'caught %', SQLERRM;
11. Using SQLSTATE Variable
IF SQLSTATE = '23505' THEN ... END IF;
12. Configuring Error Logging
log_min_messages = warning
log_min_error_statement = error
log_statement = ddl
log_line_prefix = '%m [%p] %u@%d %r '
13. Creating Custom Exceptions
RAISE EXCEPTION USING ERRCODE = 'P0001', MESSAGE = 'business rule X violated';
-- Catch:
EXCEPTION WHEN SQLSTATE 'P0001' THEN ...