Right, let’s talk about error handling. Because your code will break. It’s not a matter of if; it’s a matter of when and how loudly. The goal isn’t to prevent errors—that’s a fool’s errand. The goal is to fail gracefully, tell us what the hell went wrong, and maybe even clean up after yourself on the way out. That’s where EXCEPTION and RAISE come in. Think of them as your code’s emergency broadcast system and its fire extinguisher.

Without them, any error in your function will bubble up to the client as a full-stop, screaming halt, potentially leaving half-finished transactions littered about your database like a toddler’s toys. We’re better than that.

The Anatomy of a BEGIN…EXCEPTION Block

You already know a BEGIN...END block. To add error handling, you insert an EXCEPTION section right before the END. The magic is that when any error occurs inside the BEGIN section, PostgreSQL immediately jumps to the EXCEPTION block, looking for a matching condition. It’s a GOTO, but for once, it’s a good and noble use of one.

CREATE OR REPLACE FUNCTION transfer_funds(
    from_account INT,
    to_account INT,
    amount DECIMAL(15,2)
) RETURNS void AS $$
BEGIN
    -- Debit the sender
    UPDATE accounts SET balance = balance - amount WHERE id = from_account;
    -- Credit the receiver
    UPDATE accounts SET balance = balance + amount WHERE id = to_account;
EXCEPTION
    WHEN OTHERS THEN
        -- Uh oh. Something above went terribly wrong.
        RAISE NOTICE 'Transfer failed. Rolling back our changes inside the function.';
        -- The function's internal transaction is rolled back to the savepoint...
        -- but more on that crucial point in a second.
END;
$$ LANGUAGE plpgsql;

Catching Specific Exceptions (The WHEN Clause)

Catching WHEN OTHERS is like using a giant net to catch “a fish.” It works, but you have no idea if you’ve caught a trout or a boot. You should be more specific. PostgreSQL has a delightful menagerie of error codes you can catch. Some of the most common ones you’ll wrestle with:

  • unique_violation (23505): You tried to insert a duplicate. Shocking.
  • foreign_key_violation (23503): You referenced a row that doesn’t exist. Classic.
  • not_null_violation (23502): You left a NOT NULL column empty. Whoops.
  • check_violation (23514): You broke a CHECK constraint. Rebel.
  • division_by_zero (22012): You tried to divide by zero. The computer is still mad about this.

You catch them by their symbolic name, which is way more readable than remembering code ‘P0001’.

CREATE OR REPLACE FUNCTION insert_user(email TEXT, name TEXT)
RETURNS void AS $$
BEGIN
    INSERT INTO users(email, full_name) VALUES (email, name);
EXCEPTION
    WHEN unique_violation THEN
        RAISE EXCEPTION 'Sorry, the email "%" is already taken.', email;
    WHEN not_null_violation THEN
        RAISE EXCEPTION 'Please provide both an email and a name.';
END;
$$ LANGUAGE plpgsql;

Now, instead of the user getting a cryptic SQL error message with a code, they get a clear, human-readable message you wrote. This is a massive upgrade for anyone using your API.

The RAISE Statement: Yelling into the Log

RAISE is how you communicate. It can be a debug message, a warning, or a full-blown error that stops execution. Its power level is up to you.

  • RAISE NOTICE 'Value is %', my_var; - A friendly log message. The % is a placeholder.
  • RAISE WARNING 'This seems odd...'; - A more urgent log message.
  • RAISE EXCEPTION 'Nope. Cannot do that.'; - This immediately terminates the function and rolls back to the implicit savepoint (more on this next). You can also use format strings: RAISE EXCEPTION 'Value % is too high', input_value;.

The Crucial, Gotcha-Laden Detail: Implicit Savepoints

Here’s the part the manual often glosses over, and it’s the most important thing you’ll learn here. PostgreSQL automatically creates a savepoint around any BEGIN...EXCEPTION block.

Let’s read that again. When you enter a block with an EXCEPTION clause, it’s like you silently issued a SAVEPOINT command. If an error occurs and is caught, the database rolls back to the state it was in at the beginning of this block. Any changes made inside the block are undone, but the transaction as a whole continues.

This is a lifesaver. It means your transfer_funds function can fail safely without aborting the entire surrounding transaction. But it’s also a mind-bender. Look at this:

CREATE OR REPLACE FUNCTION confusing_example()
RETURNS void AS $$
BEGIN
    INSERT INTO table_a (value) VALUES ('Outside');
    BEGIN -- This inner BEGIN starts a sub-block with an implicit savepoint
        INSERT INTO table_a (value) VALUES ('Inside - Will be rolled back');
        RAISE EXCEPTION 'Fail here!';
    EXCEPTION
        WHEN OTHERS THEN
            RAISE NOTICE 'Caught the error!';
    END;
    INSERT INTO table_a (value) VALUES ('Outside again');
END;
$$ LANGUAGE plpgsql;

If you run this function, what ends up in table_a?

  1. ‘Outside’ - It was inserted before the sub-block.
  2. ‘Outside again’ - It was inserted after the sub-block’s error was caught.

The entry ‘Inside - Will be rolled back’ is gone. The RAISE EXCEPTION triggered a rollback to the implicit savepoint, wiping it out. This behavior is perfect for making functions atomic, but you must be aware of it. It means variables set inside the block will also revert if an error is caught!

Best Practices and Pitfalls

  1. Don’t Overcatch: Only catch exceptions you can actually handle. If you don’t know what to do with an error, often the best thing is to let it propagate up. Catching OTHERS and then just logging it can hide critical failures. Use it sparingly.
  2. Re-raise When in Doubt: If you catch an error to log it but can’t actually resolve it, use RAISE to throw it again. You can even chain the original error: RAISE EXCEPTION 'My context failed: %', SQLERRM;.
  3. Performance Cost: Blocks with EXCEPTION clauses are significantly more expensive to enter than those without. The database has to prepare for that rollback. Don’t wrap every single statement in one; group operations that need to be atomic together into a single block.
  4. Use RAISE for Debugging: Sprinkle RAISE NOTICE 'Reached point A, value is %', my_var; statements when developing. It’s the simplest debugger you have. Just remember to remove or comment them out before moving to production unless you want your logs to be incredibly noisy.

Mastering this turns you from someone who writes scripts into someone who engineers resilient, trustworthy systems. It’s the difference between a cardboard fort and a brick wall. Now go build some brick walls.