SQL EXCEPTION

What is the SQL EXCEPTION keyword?

EXCEPTION designates error-handling blocks and raises errors in PostgreSQL PL/pgSQL code.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL EXCEPTION: PostgreSQL (full support), Amazon Redshift (partial), EDB Advanced Server. Not available in MySQL, SQL Server, SQLite, or standard SQL; Oracle uses a similar EXCEPTION section in PL/SQL but with different syntax.

SQL EXCEPTION Full Explanation

In PostgreSQL PL/pgSQL, EXCEPTION plays two distinct roles. First, it starts the error-handling section of a BEGIN … END block, allowing developers to trap specific runtime errors and execute recovery logic instead of aborting the session. Second, used with the RAISE statement (RAISE EXCEPTION), it deliberately throws an error, immediately aborting the current transaction block unless caught by an enclosing EXCEPTION handler. Both forms integrate with PostgreSQL’s transactional semantics: once an uncaught exception is raised, the transaction enters an aborted state and must be rolled back or ended. EXCEPTION blocks match errors by condition name (e.g., unique_violation) or SQLSTATE codes, support multiple OR-chained conditions, and default to WHEN OTHERS for a catch-all. Because EXCEPTION is PL/pgSQL-only, it cannot appear in plain SQL executed outside a function, DO block, or procedure. Raising an exception rolls back any changes made in the current block unless handled, so care is needed when nesting subtransactions (e.g., using SAVEPOINT).

SQL EXCEPTION Syntax

-- EXCEPTION block inside a PL/pgSQL function or DO block
BEGIN
    <normal_statements>;
EXCEPTION
    WHEN condition [OR condition ...] THEN
        <handler_statements>;
END;

-- Raising an error
RAISE EXCEPTION 'error message format', arg1, arg2 [, ...] [USING option = value [, ...]];

SQL EXCEPTION Parameters

- condition (text or SQLSTATE) - Name such as unique_violation, division_by_zero, or a 5-char SQLSTATE code.
- message (text) - Required. Error string (can include format specifiers like %).
- argN (any) - Optional. Values to substitute into message.
- USING option=value - keyword pairs to set errcode, detail, hint, column, table, constraint, schema.

Example Queries Using SQL EXCEPTION

-- 1. Trap a duplicate key error and fall back to update
DO $$
BEGIN
    INSERT INTO users(id,email) VALUES (1,'a@getgalaxy.io');
EXCEPTION WHEN unique_violation THEN
    UPDATE users SET email='a@getgalaxy.io' WHERE id=1;
END$$;

-- 2. Raise a custom exception when business rule fails
CREATE OR REPLACE FUNCTION enforce_positive(p_num int) RETURNS void AS $$
BEGIN
    IF p_num <= 0 THEN
        RAISE EXCEPTION 'Input must be positive. Got: %', p_num
            USING ERRCODE = '22023', HINT = 'Pass a value > 0';
    END IF;
END$$ LANGUAGE plpgsql;

Expected Output Using SQL EXCEPTION

First block inserts a new row, or if the row exists, updates it without aborting the transaction.
Second function will abort with SQLSTATE 22023 and the custom message when called with a non-positive integer.

Use Cases with SQL EXCEPTION

Gracefully handling constraint violations during upsert-like operations
Logging and re-throwing errors with additional context
Converting low-level SQL errors into domain-specific messages
Enforcing business rules that are hard to express with pure SQL constraints

Common Mistakes with SQL EXCEPTION

Writing EXCEPTION outside of a PL/pgSQL BEGIN … END block (results in syntax error)
Forgetting that an uncaught exception puts the entire transaction in aborted state
Catching WHEN OTHERS without re-raising or logging, which hides real problems
Using generic SQLSTATE codes instead of specific condition names, reducing readability

First Introduced In

PostgreSQL 7.0 (initial PL/pgSQL support)

Frequently Asked Questions

What is the difference between EXCEPTION and RAISE EXCEPTION?

EXCEPTION starts a catch block, while RAISE EXCEPTION actively throws a new error. Both interact but serve opposite roles: catching vs raising.

Can I handle multiple error types in one block?

Yes. Chain conditions with OR, or write separate WHEN clauses for fine-grained handling.

How do I find valid condition names?

Query the pg_catalog.pg_exception table or use the official documentation listing condition names and SQLSTATE codes.

Is EXCEPTION supported in PostgreSQL procedures?

Absolutely. Procedures written in PL/pgSQL use the same BEGIN … EXCEPTION … END syntax as functions.