EXCEPTION blocks in SQL

Division by zero, caught by condition name; the block's work rolls back first.

-- EXCEPTION catches by condition name; the block's work rolls back.
DO $$
DECLARE
    ratio NUMERIC;
BEGIN
    ratio := 10 / 0;
    RAISE NOTICE 'never reached: %', ratio;
EXCEPTION
    WHEN division_by_zero THEN
        RAISE NOTICE 'caught: dividing by zero';
    WHEN OTHERS THEN
        RAISE NOTICE 'caught something else: %', SQLERRM;
END;
$$;

How it works

ratio := 10 / 0 aborts the block mid-flight.
EXCEPTION WHEN division_by_zero THEN names the condition precisely.
WHEN OTHERS is the catch-all, with SQLERRM holding the message.

Keywords and builtins used here

BEGIN
DECLARE
DO
END
EXCEPTION
NUMERIC
THEN
WHEN

The run, in numbers

Lines: 14
Characters to type: 314
Tokens: 44
Three-star pace: 65 tpm

At the three-star pace of 65 tokens a minute, this run takes about 41 seconds.

Type this snippet

Step 3 of 4 in Errors & triggers, step 19 of 23 in PostgreSQL Stored Procedures.

← Previous Next →