PL/pgSQL Exception Handling

Master PL/pgSQL exception handling — BEGIN/EXCEPTION/WHEN syntax, NO_DATA_FOUND and TOO_MANY_ROWS via SELECT INTO STRICT, the FOUND special variable, and SQLERRM/SQLSTATE inspection. Critical fixes: original INSERT had duplicate primary key rows, INTO STRICT used an undeclared variable, and table name mismatched between create and reference.

An exception is what PL/pgSQL raises when something goes wrong during execution: a query that returns no rows when the code expected exactly one, an integer overflow, a division by zero, a unique-constraint violation, a null value where one isn't allowed. By default, an exception aborts the current block (and the surrounding transaction).

You can catch exceptions and respond gracefully using an exception handler — a section of the block that runs only when something goes wrong. The pattern is borrowed from procedural languages like Ada and Oracle PL/SQL, but PostgreSQL has its own set of named conditions and SQLSTATE codes.

Three things to know:

• Exception handlers are checked top-to-bottom; the first matching WHEN wins.
• WHEN OTHERS catches anything not matched above. Place it last.
• An exception handler can also reference exceptions by SQLSTATE: WHEN SQLSTATE 'P0002' THEN.

DROP TABLE IF EXISTS customer;

CREATE TABLE customer (
    customer_id INT PRIMARY KEY,
    name        TEXT
);

INSERT INTO customer (customer_id, name) VALUES
    (1, 'John Doe'),
    (2, 'Jane Smith'),
    (3, 'Alice Johnson'),
    (4, 'Mark Johnson'),
    (5, 'Emily Brown'),
    (6, 'David Smith');

A plain SELECT INTO doesn't raise an exception when no row is found — it just leaves the target variable as NULL. Look at this:

DO $$
DECLARE
    l_name        TEXT;
    l_customer_id INT := 999;            -- no such customer
BEGIN
    SELECT name INTO l_name
    FROM   customer
    WHERE  customer_id = l_customer_id;

    RAISE NOTICE 'Customer name is: %', l_name;
END
$$;

No exception, just a NULL — easy to miss in production code. Two ways to detect this case explicitly:

1. Check for NULL after the SELECT with an IF l_name IS NULL THEN
2. Use SELECT INTO STRICT — raises no_data_found when zero rows return, too_many_rows when more than one

SELECT INTO STRICT requires exactly one row. Anything else raises an exception you can catch:

DO $$
DECLARE
    l_name        TEXT;
    l_customer_id INT := 999;
BEGIN
    SELECT name INTO STRICT l_name
    FROM   customer
    WHERE  customer_id = l_customer_id;

    RAISE NOTICE 'Customer name is: %', l_name;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RAISE NOTICE 'Customer % does not exist', l_customer_id;
    WHEN TOO_MANY_ROWS THEN
        RAISE NOTICE 'More than one customer matches % — data integrity issue?',
                     l_customer_id;
END
$$;

Now we get a clear "does not exist" message. The exception was raised, caught, handled, and execution continued past the block.

An internally raised exception. The SQL engine raises division_by_zero when math goes south:

DO $$
DECLARE
    a NUMERIC := 100;
    b NUMERIC := 0;
    result NUMERIC;
BEGIN
    result := a / b;                       -- raises division_by_zero
    RAISE NOTICE 'Result: %', result;
EXCEPTION
    WHEN division_by_zero THEN
        RAISE NOTICE 'Cannot divide by zero — using NULL instead';
        result := NULL;
END
$$;

An alternative to STRICT for the "no rows" case: do a non-STRICT SELECT, then check the special FOUND variable. FOUND is automatically set to TRUE when the last query returned at least one row, FALSE otherwise:

DO $$
DECLARE
    l_name        TEXT;
    l_customer_id INT := 1;
BEGIN
    SELECT name INTO l_name
    FROM   customer
    WHERE  customer_id = l_customer_id;

    IF FOUND THEN
        RAISE NOTICE 'Customer found: %', l_name;
    ELSE
        RAISE NOTICE 'No customer with ID %', l_customer_id;
    END IF;
END
$$;

A common internally-raised exception: trying to INSERT a row whose primary key (or other UNIQUE column) already exists. The SQL engine raises unique_violation:

DO $$
BEGIN
    INSERT INTO customer (customer_id, name)
    VALUES (1, 'Duplicate John');           -- 1 already exists
    RAISE NOTICE 'Inserted successfully';
EXCEPTION
    WHEN unique_violation THEN
        RAISE NOTICE 'Customer ID already exists — skipping';
END
$$;

You can reference any of these by name in WHEN clauses, or by SQLSTATE: WHEN SQLSTATE '23505' THEN. Use names when they exist; SQLSTATE for codes that don't have a friendly name.

Inside an exception handler, two special variables tell you what just happened:

• SQLERRM — the human-readable error message text
• SQLSTATE — the 5-character SQLSTATE code (e.g. '23505')

DO $$
BEGIN
    INSERT INTO customer (customer_id, name) VALUES (1, NULL);  -- name is NOT NULL
EXCEPTION
    WHEN OTHERS THEN
        RAISE NOTICE 'Caught error: %', SQLERRM;
        RAISE NOTICE 'SQLSTATE was: %', SQLSTATE;
END
$$;

1. Catch specific exceptions, not OTHERS, when you can. A specific handler shows readers what failure modes you've considered. WHEN OTHERS is a catch-all — handy as a last resort, but it can swallow bugs you'd rather see raised.
2. Use SELECT INTO STRICT when you mean "exactly one row." Catches both "no rows" and "more than one" cases — better than relying on FOUND or NULL checks.
3. Don't use exceptions for normal control flow. Catching an exception costs more than checking with IF. For the "if exists, update; else insert" pattern, prefer INSERT ... ON CONFLICT over a try-insert-catch-then-update loop.
4. Reraise unknown exceptions in WHEN OTHERS handlers. If you can't actually handle an exception, log what you can and use a bare RAISE; to let it propagate. Don't silently swallow.
5. Prefer named exceptions to SQLSTATE when a name exists — more readable. Use SQLSTATE for codes without friendly names.
6. Be aware that the entire block's effects are rolled back when an exception occurs. If you've INSERTed and UPDATEd before the failing line, those are undone before the handler runs.

• An exception handler section in a PL/pgSQL block lets you catch errors and respond gracefully instead of letting them abort the transaction.
• The syntax is BEGIN ... EXCEPTION WHEN exception_name THEN handler ... END.
• Use SELECT INTO STRICT when you want "exactly one row" — raises no_data_found or too_many_rows on mismatch.
• Plain SELECT INTO sets the variable to NULL on no match — combine with FOUND or an explicit NULL check.
• Use named exceptions (like division_by_zero, unique_violation) when they exist; use SQLSTATE for codes without names.
• Inside a handler, SQLERRM gives the message text; SQLSTATE gives the 5-character code.
• Don't use exceptions for normal control flow — they have a cost. WHEN OTHERS is a last-resort catch-all.