BrevFeed
Sign in

39. Error handling: RAISE and EXCEPTION blocks

📖 Reading · 5 min

RAISE: generating your own errors and messages

RAISE sends a message at a chosen severity level — from informational output to a hard error that aborts the transaction:

CREATE FUNCTION set_rental_rate(p_film_id integer, p_new_rate numeric)
RETURNS void
LANGUAGE plpgsql
AS $$
BEGIN
    IF p_new_rate < 0 THEN
        RAISE EXCEPTION 'rental_rate cannot be negative, got %', p_new_rate;
    END IF;

    IF p_new_rate > 20 THEN
        RAISE WARNING 'rental_rate % is unusually high for film %', p_new_rate, p_film_id;
    END IF;

    UPDATE film SET rental_rate = p_new_rate WHERE film_id = p_film_id;
END;
$$;

Severity levels, roughly ascending: DEBUG, LOG, NOTICE (you've seen this one throughout the course, e.g. "table does not exist, skipping"), WARNING, EXCEPTION. Only EXCEPTION actually aborts execution (and the enclosing transaction — "one error poisons the whole transaction", a rule Module 12 covers properly — unless caught, see below); the others just print a message and continue. % in the message string is a placeholder, filled positionally by the arguments after it — directly analogous to a format string in most general-purpose languages.

Why this matters beyond just "printing messages"

RAISE EXCEPTION is how a function rejects invalid input or an invalid state on its own terms, with a message that explains why — compare this to letting a constraint violation (Module 2) surface a generic, less specific database error. Both approaches enforce correctness; RAISE EXCEPTION lets you attach domain-specific validation logic and a clear message where a simple CHECK constraint's boolean expression isn't expressive enough.

Catching exceptions: EXCEPTION blocks

Recall the transaction-aborting-on-error behavior above (Module 12 covers it properly) — this is where PL/pgSQL gives you a way to actually recover, inside a function, rather than needing a SAVEPOINT from the calling code:

CREATE FUNCTION safe_divide(p_numerator numeric, p_denominator numeric)
RETURNS numeric
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN p_numerator / p_denominator;
EXCEPTION
    WHEN division_by_zero THEN
        RAISE NOTICE 'division by zero caught, returning NULL instead';
        RETURN NULL;
END;
$$;

SELECT safe_divide(10, 2);   -- 5
SELECT safe_divide(10, 0);   -- NULL, with a NOTICE, not a hard error

A BEGIN ... EXCEPTION WHEN condition THEN ... END block catches a specific, named error condition (division_by_zero, unique_violation, foreign_key_violation, not_null_violation, and many others — Postgres has a large, documented catalog of these) and runs recovery code instead of propagating the error further. WHEN OTHERS THEN catches anything not more specifically matched — useful as a last resort, but catching too broadly hides bugs: a WHEN OTHERS block that silently swallows every error can mask a genuine problem (a typo'd column name, an unexpected NULL) as if it were the specific condition you meant to handle. Catch the narrowest condition that actually describes the situation you're handling.

Practical example: catching a real constraint violation

(Demonstrated here against a small scratch table with its own UNIQUE constraint — pagila's real customer.email column, notably, has no uniqueness constraint at all in this schema, so this couldn't be shown against it directly; worth noting as a reminder that a schema's actual constraints are always worth checking with \d, never assumed from a column's name.)

CREATE TABLE subscriber (id serial PRIMARY KEY, email text UNIQUE NOT NULL);

CREATE FUNCTION add_subscriber_safe(p_email text)
RETURNS text
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO subscriber (email) VALUES (p_email);
    RETURN 'created';
EXCEPTION
    WHEN unique_violation THEN
        RETURN 'already exists';
END;
$$;

This function turns a database-level constraint error into a normal, handleable return value — the calling application checks the returned string instead of needing to catch and inspect a database exception itself. This pattern (catch a specific, expected constraint violation, translate it into an ordinary result) is one of the most common, genuinely useful applications of PL/pgSQL exception handling in real schemas.

Check yourself

  1. What's the difference between RAISE WARNING and RAISE EXCEPTION in terms of whether execution continues?
  2. Why is catching WHEN OTHERS THEN broadly considered risky, even though it's convenient?
  3. What does the add_subscriber_safe example do differently from just letting the unique_violation propagate as a raw database error?
← Previous Next →