Right, so you’ve wrapped a chunk of your logic in a BEGIN...COMMIT transaction. Good for you. You’re protecting the integrity of your data from partial failures. But here’s the thing: what happens when one of those statements inside the transaction fails? The database will get cranky, raise an error, and abort the entire transaction. Your brilliant, all-or-nothing logic becomes a very definitive “nothing.” Sometimes that’s exactly what you want. But often, you’d prefer a slightly more nuanced approach: “Okay, that specific operation blew up, but can we maybe just log the failure and carry on with the rest?”

This is where PL/pgSQL’s EXCEPTION blocks come in. They’re your tactical error handlers inside the larger strategic war of a transaction. They let you catch an error, deal with it locally, and allow your procedure to continue executing as if nothing happened. It’s powerful, but like most powerful things, it’s easy to accidentally blow your own foot off.

The Anatomy of a BEGIN/EXCEPTION/END Block

Think of this as a TRY/CATCH construct grafted onto a SQL statement. The basic structure looks like this:

BEGIN
    -- Some operation that might fail
    ...
EXCEPTION
    WHEN exception_condition THEN
        -- Your brilliant recovery code
        ...
END;

The key here is that this BEGIN is not the start of a transaction. It’s the start of a PL/pgSQL block inside your transaction. This trips people up constantly. You’ll usually see this inside a function or a DO block that’s itself running inside a transaction.

Let’s get concrete. Imagine a process where you add a new user and assign them a default profile. Inserting the user might fail (duplicate email?), but you don’t want that to prevent you from creating the profile for other, successful user inserts.

CREATE OR REPLACE PROCEDURE create_user_and_profile(
    p_email varchar,
    p_name varchar
)
LANGUAGE plpgsql
AS $$
BEGIN
    -- Start a transaction block (this is the actual transaction BEGIN)
    BEGIN
        INSERT INTO users(email, name) VALUES (p_email, p_name);

        -- This is a sub-block with its own error handling
        BEGIN
            INSERT INTO profiles(user_id, theme) 
            VALUES (currval('users_id_seq'), 'light');
        EXCEPTION
            WHEN others THEN
                RAISE NOTICE 'Could not create profile for new user. Continuing anyway.';
        END;

    COMMIT;
    END;
END;
$$;

In this example, if the profiles insert fails (maybe there’s a trigger we forgot about?), it will be caught by the inner EXCEPTION block. A notice is logged, and the procedure happily continues to COMMIT, persisting the user even though the profile step failed. This might be exactly what you want. Or it might be a data integrity nightmare. You have to decide.

The Cost of Safety: Sub-Transactions and Performance

Here’s the part the manual often buries in the fine print: every time you enter an EXCEPTION block in PL/pgSQL, PostgreSQL secretly creates a subtransaction to encapsulate the code inside that block.

Why? Because if something goes wrong inside the block, the database needs to be able to roll back just the operations within that block without affecting the outer transaction. This is a brilliant design for correctness but a potential nightmare for performance.

Each subtransaction requires its own overhead. If you wrap a tight loop that executes 100,000 times in a block with an exception handler, you are asking PostgreSQL to create 100,000 potential subtransactions. The performance hit is significant. Use exception blocks judiciously, not as a default wrapper for every single statement.

Getting Specific with Your Exceptions

Catching WHEN others THEN is like using a net to catch a specific type of butterfly—you’ll get the butterfly, but also every bee, leaf, and piece of garbage in the area. It’s a blunt instrument. You can and should be more precise.

PostgreSQL has a whole hierarchy of error codes. You can catch them by their SQLSTATE code or by their named condition. This lets you handle different failures in different ways.

BEGIN
    UPDATE accounts SET balance = balance - 100.00 WHERE user_id = 1;
EXCEPTION
    WHEN unique_violation THEN
        RAISE NOTICE 'Duplicate key!';
    WHEN check_violation THEN
        RAISE NOTICE 'Check constraint failed!';
    WHEN others THEN
        RAISE NOTICE 'Something else went wrong: %', SQLERRM;
END;

This is a much more robust approach. Maybe a check_violation means you can try an alternative value, while a unique_violation is a show-stopper. Handling them separately makes your code smarter and more resilient.

The Gotchas: The Silent Rollback

The most important thing to remember is what happens when you catch an exception: the entire inner block is rolled back. All work done within the BEGIN block that led to the EXCEPTION is undone. It’s as if it never happened.

Look at this code:

BEGIN
    INSERT INTO table_a (value) VALUES ('First');
    
    BEGIN -- Inner block starts
        INSERT INTO table_b (value) VALUES ('This will fail due to a constraint');
        INSERT INTO table_a (value) VALUES ('Second');
    EXCEPTION
        WHEN others THEN
            RAISE NOTICE 'Caught an error!';
    END; -- Inner block ends

    INSERT INTO table_a (value) VALUES ('Third');
COMMIT;
END;

What gets committed? Only 'First' and 'Third' in table_a. The entire inner block—the failed insert into table_b and the subsequent insert of 'Second' into table_a—is rolled back when the exception is caught. The EXCEPTION block catches the error, but it doesn’t magically undo the rollback of the subtransaction. This catches people out all the time. The error handler isn’t there to fix the data; it’s there to respond to the fact that the operation was aborted.