PL/pgSQL RAISE Statement

Master RAISE for exceptions and diagnostics — DEBUG/LOG/INFO/NOTICE/WARNING/EXCEPTION levels, USING clauses for ERRCODE/HINT/DETAIL, and user-defined exceptions via SQLSTATE codes. Critical correction: PostgreSQL has NO Oracle-style 'my_exc EXCEPTION;' declaration syntax — that does not work in PL/pgSQL. Use SQLSTATE-based identification instead.

The previous page covered catching exceptions. This page is about raising them — both to report errors from your own code and to send diagnostic messages to clients without interrupting execution.

The RAISE statement does double duty in PL/pgSQL:

Only EXCEPTION level actually throws an exception. The other levels just emit diagnostic messages and let execution continue.

The format string can include % placeholders, like printf. Each % consumes the next argument:

RAISE NOTICE 'Hello, %', 'world';
RAISE NOTICE 'Got % rows for user %', row_count, user_id;
RAISE EXCEPTION 'Invalid age: %', age_value;

To include a literal % in the message, use %%.

If you're coming from Oracle PL/SQL, you'll have seen exception declarations like this:

-- Oracle PL/SQL pattern — does NOT work in PostgreSQL
DECLARE
    my_exception EXCEPTION;     -- Oracle-only syntax
BEGIN
    RAISE my_exception;
END;

DROP TABLE IF EXISTS employee;

CREATE TABLE employee (
    employee_id SERIAL PRIMARY KEY,
    name        TEXT NOT NULL,
    salary      NUMERIC(10, 2) NOT NULL
);

INSERT INTO employee (name, salary) VALUES
    ('John Doe',      50000),
    ('Jane Smith',    60000),
    ('Alice Johnson', 55000);

The simplest way to raise an exception: just include a message. Validation failures are the canonical use case:

DO $$
DECLARE
    employee_name TEXT    := 'John Doe';
    new_salary    NUMERIC := 45000;
    min_salary    NUMERIC := 50000;
BEGIN
    IF new_salary < min_salary THEN
        RAISE EXCEPTION 'Salary %.2f is below minimum %.2f for %',
                        new_salary, min_salary, employee_name;
    END IF;

    UPDATE employee
    SET    salary = new_salary
    WHERE  name = employee_name;

    RAISE NOTICE 'Updated salary for %', employee_name;
END
$$;

The transaction aborts — the UPDATE never runs. Important: the error is reported with default SQLSTATE P0001 (raise_exception), since we didn't supply one explicitly.

NOTICE-level messages don't abort anything — they just print and execution continues. Useful for logging progress through a long operation:

DO $$
DECLARE
    rec record;
    cnt INTEGER := 0;
BEGIN
    RAISE NOTICE 'Starting employee scan...';

    FOR rec IN SELECT name, salary FROM employee ORDER BY name LOOP
        cnt := cnt + 1;
        RAISE NOTICE '  [%] % → %', cnt, rec.name, rec.salary;
    END LOOP;

    RAISE NOTICE 'Scan complete. % rows seen.', cnt;
END
$$;

USING attaches structured metadata to the exception — error code, hint, detail, error position, and more. Clients and exception handlers can read these fields separately from the message text.

This is the PostgreSQL way to define and raise "user-defined" exceptions: pick a SQLSTATE code (the P-prefixed range is reserved for user-defined codes), and use it consistently in both RAISE and the matching catch handler:

DO $$
DECLARE
    avg_salary NUMERIC;
BEGIN
    SELECT AVG(salary) INTO avg_salary FROM employee;

    BEGIN
        IF avg_salary > 50000 THEN
            RAISE EXCEPTION 'Average salary too high: %.2f', avg_salary
                USING ERRCODE = 'P0900',
                      HINT    = 'Review compensation policy',
                      DETAIL  = 'Maximum allowed is 50000';
        END IF;
    EXCEPTION
        WHEN SQLSTATE 'P0900' THEN
            RAISE NOTICE 'Caught policy violation: %', SQLERRM;
    END;
END
$$;

Inside an exception handler, a bare RAISE; reraises the current exception, letting it propagate to enclosing blocks. Useful when you want to log something locally before letting the error continue:

DO $$
BEGIN
    BEGIN
        -- Inner block: simulates a problem
        RAISE EXCEPTION 'Something went wrong'
            USING ERRCODE = 'P0901';
    EXCEPTION
        WHEN SQLSTATE 'P0901' THEN
            RAISE NOTICE 'Inner block caught and logging: %', SQLERRM;
            RAISE;          -- bare RAISE: reraise current exception
    END;
EXCEPTION
    WHEN SQLSTATE 'P0901' THEN
        RAISE NOTICE 'Outer block caught the propagated exception';
END
$$;

You can also raise built-in exceptions by name. Useful when your code's logic implies a particular failure mode:

DO $$
DECLARE
    divisor INTEGER := 0;
BEGIN
    BEGIN
        IF divisor = 0 THEN
            RAISE division_by_zero;
        END IF;
        RAISE NOTICE 'OK';
    EXCEPTION
        WHEN division_by_zero THEN
            RAISE NOTICE 'Caught: division by zero (divisor was zero)';
    END;
END
$$;

The PostgreSQL approach is more lightweight (no declarations needed) but trades off the named-symbol clarity Oracle gives you. To compensate, document your custom SQLSTATE codes — pick a small range, write them down, use them consistently.

1. Use the right level for the situation. NOTICE for diagnostics, WARNING for "this seems suspicious," EXCEPTION only when you actually want to abort.
2. Include context in error messages. "Invalid age" is less useful than "Invalid age 250 for user_id 8842." The % placeholders are inexpensive — use them.
3. Pick SQLSTATE codes deliberately. Use the P-class for your own; document them. Don't overload built-in codes (like 23505) for unrelated user-defined errors.
4. Use HINT and DETAIL with USING. Tools and developers can display them separately from the main message — better diagnostics for the same effort.
5. Don't try to declare named exceptions like Oracle. my_exception EXCEPTION; doesn't work. Use SQLSTATE-based identification instead.
6. Reraise unknown exceptions instead of swallowing them. A bare RAISE; in WHEN OTHERS is much safer than silently consuming bugs.

• RAISE in PL/pgSQL emits messages at one of six levels: DEBUG, LOG, INFO, NOTICE (default), WARNING, EXCEPTION.
• Only RAISE EXCEPTION aborts execution. The other levels just print and continue.
• PostgreSQL has no EXCEPTION data type. The Oracle pattern my_exc EXCEPTION; doesn't work here.
• For user-defined exceptions, raise via USING ERRCODE = 'Pxxxx' and catch via WHEN SQLSTATE 'Pxxxx' THEN. The P-class is reserved for user-defined codes.
• USING attaches structured metadata (ERRCODE, HINT, DETAIL, etc.) for tooling and clients.
• A bare RAISE; inside an exception handler reraises the current exception, letting it propagate.
• Format messages with % placeholders; include enough context for the message to be useful in isolation.