SQL 86 🛢️ Error Handling and Exception Control - KandZ

⏱️ Estimated read: 17 min | |

A database operation can fail. A unique constraint is violated, a foreign key has no matching row, a division by zero occurs, a deadlock is detected, a connection drops. The error is not the exception in a well-designed system; it is part of the contract. The question is what the database does when the error happens and what the application does with the result. Procedural SQL provides the exception handling: a block that catches the errors, a handler that runs the recovery, and a mechanism that reports the failure. The syntax differs by database, but the structure is the same: try, catch, handle.

Key point: An unhandled error in a procedural block aborts the block and rolls back the transaction. A handled error lets the block continue, and the handler decides whether to commit, roll back, or retry. The handler is the boundary between the failure and the rest of the system.

Why error handling exists

The constraint problem. A unique index rejects the duplicate insert. A foreign key rejects the orphan row. A check constraint rejects the invalid value. The database enforces the rules, and the rules produce the errors. The handling is how the application turns the error into the user-facing message.

The transaction problem. A multi-statement operation must be atomic. If the third statement fails, the first two must be undone. The exception handler is where the ROLLBACK lives, and the transaction is the unit that guarantees the atomicity.

The retry problem. A deadlock is a transient failure. The correct response is to roll back and retry, and the retry succeeds. The handler is where the retry loop lives, and the transient error is the signal.

The diagnostic problem. An error has a code, a message, and the context. The handler reads the code and the message, logs the diagnostic, and returns the structured result. The application uses the structure to decide the response.

The nesting problem. The exceptions propagate up the call stack. A procedure calls a function, and the function raises. The exception is caught by the nearest handler, and the handler decides whether to handle or re-raise.

The recovery problem. Some errors are recoverable: the default value, the retry, the alternate path. Some are not: the constraint violation the application cannot fix. The handler distinguishes the two and chooses the response.

The user problem. The raw error message is not the user-facing message. The handler maps the error code to the friendly message, and the application displays it. The mapping is the API between the database and the UI.

a. The exception blocks

The procedural SQL has the exception blocks. The structure is the BEGIN ... EXCEPTION ... END in the PostgreSQL, the BEGIN TRY ... BEGIN CATCH ... END CATCH in the SQL Server, and the DECLARE ... HANDLER in the MySQL.

The PostgreSQL exception block

CREATE OR REPLACE PROCEDURE transfer_funds(
    from_account INT,
    to_account   INT,
    amount       NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE accounts SET balance = balance - amount WHERE id = from_account;

    IF (SELECT balance FROM accounts WHERE id = from_account) < 0 THEN
        RAISE EXCEPTION 'Insufficient funds' USING ERRCODE = 'P0001';
    END IF;

    UPDATE accounts SET balance = balance + amount WHERE id = to_account;
EXCEPTION
    WHEN OTHERS THEN
        RAISE NOTICE 'Transfer failed: %', SQLERRM;
        ROLLBACK;
        RAISE;
END;
$$;

The EXCEPTION block catches the error, the WHEN OTHERS matches every exception, and the RAISE re-raises after the rollback.

The SQL Server try/catch

CREATE PROCEDURE dbo.TransferFunds
    @FromAccount INT,
    @ToAccount   INT,
    @Amount      DECIMAL(12,2)
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        BEGIN TRANSACTION;

        UPDATE accounts SET balance = balance - @Amount WHERE id = @FromAccount;

        IF (SELECT balance FROM accounts WHERE id = @FromAccount) < 0
            THROW 50001, 'Insufficient funds', 1;

        UPDATE accounts SET balance = balance + @Amount WHERE id = @ToAccount;

        COMMIT;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK;

        PRINT ERROR_MESSAGE();
        THROW;
    END CATCH
END;

The TRY block runs the operation, the CATCH block handles the error, and the THROW re-raises after the rollback.

The MySQL handler

DELIMITER //

CREATE PROCEDURE transfer_funds(
    IN from_account INT,
    IN to_account   INT,
    IN amount       DECIMAL(12,2)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;
    UPDATE accounts SET balance = balance - amount WHERE id = from_account;
    UPDATE accounts SET balance = balance + amount WHERE id = to_account;
    COMMIT;
END //

DELIMITER ;

The DECLARE EXIT HANDLER registers the handler. The EXIT means the handler runs and the block exits. The SQLEXCEPTION matches every error. The RESIGNAL re-raises after the rollback.

The exception block table

Database	Structure
PostgreSQL	`BEGIN ... EXCEPTION WHEN ... THEN ... END;`
SQL Server	`BEGIN TRY ... END TRY BEGIN CATCH ... END CATCH`
MySQL	`DECLARE ... HANDLER FOR ... BEGIN ... END;`

b. The specific handlers

The WHEN OTHERS catches everything. The specific handlers catch the specific errors.

The PostgreSQL specific handler

EXCEPTION
    WHEN unique_violation THEN
        RAISE NOTICE 'The email is already registered';
    WHEN foreign_key_violation THEN
        RAISE NOTICE 'The customer does not exist';
    WHEN division_by_zero THEN
        RAISE NOTICE 'The divisor is zero';
    WHEN OTHERS THEN
        RAISE NOTICE 'Unexpected error: %', SQLERRM;
END;

The unique_violation, the foreign_key_violation, and the division_by_zero are the named conditions.

The PostgreSQL condition names

Condition	The SQLSTATE	The error
`unique_violation`	`23505`	The duplicate key
`foreign_key_violation`	`23503`	The missing reference
`not_null_violation`	`23502`	The null in the not-null column
`check_violation`	`23514`	The check constraint
`division_by_zero`	`22012`	The zero divisor
`deadlock_detected`	`40P01`	The deadlock
`serialization_failure`	`40001`	The serialization conflict
`lock_not_available`	`55P03`	The lock timeout
`raise_exception`	`P0001`	The custom `RAISE`

The SQL Server error handling

BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
    DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
    DECLARE @ErrorState INT = ERROR_STATE();
    DECLARE @ErrorNumber INT = ERROR_NUMBER();
    DECLARE @ErrorLine INT = ERROR_LINE();
    DECLARE @ErrorProcedure NVARCHAR(200) = ERROR_PROCEDURE();

    IF @@TRANCOUNT > 0 ROLLBACK;

    RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH

The ERROR_MESSAGE(), the ERROR_NUMBER(), and the others are the diagnostic functions.

The SQL Server error functions

Function	Returns
`ERROR_MESSAGE()`	The error message
`ERROR_NUMBER()`	The error code
`ERROR_SEVERITY()`	The severity
`ERROR_STATE()`	The state
`ERROR_LINE()`	The line number
`ERROR_PROCEDURE()`	The procedure name

The MySQL handler

DECLARE EXIT HANDLER FOR 1062
BEGIN
    SELECT 'Duplicate entry' AS error;
END;

The 1062 is the MySQL error code for the duplicate entry.

The MySQL condition names

DECLARE duplicate_entry CONDITION FOR SQLSTATE '23000';
DECLARE EXIT HANDLER FOR duplicate_entry
BEGIN
    SELECT 'Duplicate entry' AS error;
END;

The DECLARE ... CONDITION names the SQLSTATE, and the handler uses the name.

The MySQL error codes

The MySQL code	The SQLSTATE	The error
`1062`	`23000`	The duplicate entry
`1452`	`23000`	The foreign key
`1048`	`23000`	The not null
`3819`	`HY000`	The check constraint
`1213`	`40001`	The deadlock
`1205`	`HY000`	The lock timeout

c. The error handling patterns

The patterns below are the ones that appear in the real procedures.

The validation pattern

The procedure validates the input and raises the custom exception.

CREATE OR REPLACE PROCEDURE create_user(
    p_email TEXT,
    p_name  TEXT
)
LANGUAGE plpgsql
AS $$
BEGIN
    IF p_email !~ '^[^@]+@[^@]+$' THEN
        RAISE EXCEPTION 'Invalid email format' USING ERRCODE = '22023';
    END IF;

    IF p_name IS NULL OR length(p_name) = 0 THEN
        RAISE EXCEPTION 'The name is required' USING ERRCODE = '22004';
    END IF;

    INSERT INTO users (email, name) VALUES (p_email, p_name);
END;
$$;

The RAISE EXCEPTION with the USING ERRCODE sets the custom error code.

The upsert pattern

The procedure handles the duplicate key and updates the existing row.

CREATE OR REPLACE PROCEDURE upsert_user(
    p_email TEXT,
    p_name  TEXT
)
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO users (email, name) VALUES (p_email, p_name);
EXCEPTION
    WHEN unique_violation THEN
        UPDATE users SET name = p_name WHERE email = p_email;
END;
$$;

The unique_violation handler converts the insert into the update.

The retry pattern

The procedure retries the transient failure.

CREATE OR REPLACE PROCEDURE transfer_with_retry(
    from_account INT,
    to_account   INT,
    amount       NUMERIC,
    max_retries  INT DEFAULT 3
)
LANGUAGE plpgsql
AS $$
DECLARE
    attempt INT := 0;
BEGIN
    LOOP
        attempt := attempt + 1;
        BEGIN
            UPDATE accounts SET balance = balance - amount WHERE id = from_account;
            UPDATE accounts SET balance = balance + amount WHERE id = to_account;
            RETURN;
        EXCEPTION
            WHEN serialization_failure OR deadlock_detected THEN
                IF attempt >= max_retries THEN
                    RAISE;
                END IF;
                PERFORM pg_sleep(0.1 * attempt);
        END;
    END LOOP;
END;
$$;

The inner BEGIN ... EXCEPTION block is the retry unit. The serialization_failure and the deadlock_detected are the transient errors.

The logging pattern

The procedure logs the error and re-raises.

CREATE OR REPLACE PROCEDURE process_order(p_order_id INT)
LANGUAGE plpgsql
AS $$
BEGIN
    -- The operation
    UPDATE orders SET status = 'processed' WHERE id = p_order_id;
EXCEPTION
    WHEN OTHERS THEN
        INSERT INTO error_log (procedure_name, error_code, error_message, created_at)
        VALUES ('process_order', SQLSTATE, SQLERRM, NOW());
        RAISE;
END;
$$;

The SQLSTATE and the SQLERRM are the diagnostic values. The RAISE re-raises after the log.

The savepoint pattern

The procedure uses the savepoint to roll back the partial work.

CREATE OR REPLACE PROCEDURE batch_insert(p_items JSONB)
LANGUAGE plpgsql
AS $$
DECLARE
    item JSONB;
BEGIN
    FOR item IN SELECT * FROM jsonb_array_elements(p_items) LOOP
        BEGIN
            INSERT INTO items (name, price) VALUES (item->>'name', (item->>'price')::NUMERIC);
        EXCEPTION
            WHEN unique_violation THEN
                RAISE NOTICE 'Skipping duplicate: %', item->>'name';
        END;
    END LOOP;
END;
$$;

The inner BEGIN ... EXCEPTION block is the savepoint. The failed insert is rolled back, and the loop continues.

The transaction abort pattern

The procedure rolls back the entire transaction on the error.

CREATE OR REPLACE PROCEDURE place_order(p_customer_id INT, p_items JSONB)
LANGUAGE plpgsql
AS $$
DECLARE
    v_order_id INT;
BEGIN
    INSERT INTO orders (customer_id) VALUES (p_customer_id) RETURNING id INTO v_order_id;

    INSERT INTO order_items (order_id, product_id, quantity)
    SELECT v_order_id, (item->>'product_id')::INT, (item->>'quantity')::INT
    FROM jsonb_array_elements(p_items) AS item;

    UPDATE inventory SET quantity = quantity - (item->>'quantity')::INT
    FROM jsonb_array_elements(p_items) AS item
    WHERE inventory.product_id = (item->>'product_id')::INT;

    IF EXISTS (SELECT 1 FROM inventory WHERE quantity < 0) THEN
        RAISE EXCEPTION 'Insufficient inventory';
    END IF;

    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
$$;

The ROLLBACK in the handler undoes the entire transaction.

The pattern table

Pattern	Purpose
Validation	The custom exception
Upsert	The duplicate key to the update
Retry	The transient failure
Logging	The diagnostic record
Savepoint	The partial rollback
Transaction abort	The full rollback

Complete Example Session

-- ============================================
-- PART 1: THE BASIC EXCEPTION BLOCK
-- ============================================

CREATE OR REPLACE PROCEDURE transfer_funds(
    from_account INT,
    to_account   INT,
    amount       NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE accounts SET balance = balance - amount WHERE id = from_account;
    UPDATE accounts SET balance = balance + amount WHERE id = to_account;
EXCEPTION
    WHEN OTHERS THEN
        RAISE NOTICE 'Transfer failed: %', SQLERRM;
        RAISE;
END;
$$;

-- ============================================
-- PART 2: THE SPECIFIC HANDLER
-- ============================================

EXCEPTION
    WHEN unique_violation THEN
        RAISE NOTICE 'The email is already registered';
    WHEN foreign_key_violation THEN
        RAISE NOTICE 'The customer does not exist';
    WHEN OTHERS THEN
        RAISE NOTICE 'Unexpected error: %', SQLERRM;
END;

-- ============================================
-- PART 3: THE SQL SERVER TRY/CATCH
-- ============================================

BEGIN TRY
    BEGIN TRANSACTION;
    UPDATE accounts SET balance = balance - @Amount WHERE id = @FromAccount;
    COMMIT;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK;
    DECLARE @Message NVARCHAR(4000) = ERROR_MESSAGE();
    RAISERROR(@Message, 16, 1);
END CATCH

-- ============================================
-- PART 4: THE MYSQL HANDLER
-- ============================================

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
    ROLLBACK;
    RESIGNAL;
END;

-- ============================================
-- PART 5: THE VALIDATION PATTERN
-- ============================================

IF p_email !~ '^[^@]+@[^@]+$' THEN
    RAISE EXCEPTION 'Invalid email format' USING ERRCODE = '22023';
END IF;

-- ============================================
-- PART 6: THE UPSERT PATTERN
-- ============================================

BEGIN
    INSERT INTO users (email, name) VALUES (p_email, p_name);
EXCEPTION
    WHEN unique_violation THEN
        UPDATE users SET name = p_name WHERE email = p_email;
END;

-- ============================================
-- PART 7: THE RETRY PATTERN
-- ============================================

LOOP
    attempt := attempt + 1;
    BEGIN
        UPDATE accounts SET balance = balance - amount WHERE id = from_account;
        RETURN;
    EXCEPTION
        WHEN serialization_failure THEN
            IF attempt >= max_retries THEN RAISE; END IF;
            PERFORM pg_sleep(0.1 * attempt);
    END;
END LOOP;

-- ============================================
-- PART 8: THE LOGGING PATTERN
-- ============================================

EXCEPTION
    WHEN OTHERS THEN
        INSERT INTO error_log (procedure_name, error_code, error_message, created_at)
        VALUES ('process_order', SQLSTATE, SQLERRM, NOW());
        RAISE;
END;

-- ============================================
-- PART 9: THE SAVEPOINT PATTERN
-- ============================================

FOR item IN SELECT * FROM jsonb_array_elements(p_items) LOOP
    BEGIN
        INSERT INTO items (name, price) VALUES (item->>'name', (item->>'price')::NUMERIC);
    EXCEPTION
        WHEN unique_violation THEN
            RAISE NOTICE 'Skipping duplicate: %', item->>'name';
    END;
END LOOP;

-- ============================================
-- PART 10: THE COMPLETE PATTERN
-- ============================================

-- 1. Use the EXCEPTION block for the error handling.
-- 2. Use the WHEN OTHERS for the catch-all.
-- 3. Use the specific handlers for the known errors.
-- 4. Use the RAISE to re-raise after the handling.
-- 5. Use the SQLSTATE and the SQLERRM for the diagnostics.
-- 6. Use the ROLLBACK in the handler for the atomicity.
-- 7. Use the inner BEGIN ... EXCEPTION for the savepoint.
-- 8. Use the loop and the sleep for the retry.
-- 9. Log the error to the table for the audit.
-- 10. Map the error code to the user-facing message.

The ten parts showed the basic exception block, the specific handler, the SQL Server try/catch, the MySQL handler, the validation pattern, the upsert pattern, the retry pattern, the logging pattern, the savepoint pattern, and the complete pattern.

Quick Reference

The Exception Block

Database	Structure
PostgreSQL	`BEGIN ... EXCEPTION WHEN ... THEN ... END;`
SQL Server	`BEGIN TRY ... END TRY BEGIN CATCH ... END CATCH`
MySQL	`DECLARE ... HANDLER FOR ... BEGIN ... END;`

The PostgreSQL Conditions

Condition	The SQLSTATE
`unique_violation`	`23505`
`foreign_key_violation`	`23503`
`not_null_violation`	`23502`
`check_violation`	`23514`
`division_by_zero`	`22012`
`deadlock_detected`	`40P01`
`serialization_failure`	`40001`
`raise_exception`	`P0001`

The SQL Server Functions

Function	Returns
`ERROR_MESSAGE()`	The message
`ERROR_NUMBER()`	The code
`ERROR_SEVERITY()`	The severity
`ERROR_STATE()`	The state
`ERROR_LINE()`	The line
`ERROR_PROCEDURE()`	The procedure

The MySQL Error Codes

Code	The SQLSTATE	The error
`1062`	`23000`	The duplicate
`1452`	`23000`	The foreign key
`1048`	`23000`	The not null
`1213`	`40001`	The deadlock

The Handling Patterns

Pattern	Mechanism
Validation	The `RAISE EXCEPTION`
Upsert	The `unique_violation` handler
Retry	The loop and the sleep
Logging	The error table
Savepoint	The inner block
Abort	The `ROLLBACK`

Best Practices

✅ Do This:

-- Use the EXCEPTION block.
BEGIN
    ...
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;  -- ✅

-- Use the specific handlers.
EXCEPTION
    WHEN unique_violation THEN ...  -- ✅

-- Use the RAISE to re-raise.
EXCEPTION
    WHEN OTHERS THEN
        RAISE;  -- ✅

-- Use the SQLSTATE and the SQLERRM.
INSERT INTO error_log (code, message) VALUES (SQLSTATE, SQLERRM);  -- ✅

-- Use the rollback in the handler.
EXCEPTION
    WHEN OTHERS THEN ROLLBACK;  -- ✅

-- Use the retry loop for the transient errors.
LOOP
    BEGIN
        ...
    EXCEPTION
        WHEN serialization_failure THEN ...
    END;
END LOOP;  -- ✅

-- Log the error for the audit.
INSERT INTO error_log ...;  -- ✅

❌ Don’t Do This:

-- Don't swallow the exception.
EXCEPTION
    WHEN OTHERS THEN NULL;  -- ❌ The error is lost

-- Don't forget the rollback.
EXCEPTION
    WHEN OTHERS THEN RAISE;  -- ⚠️ The transaction is not rolled back

-- Don't catch the exception without the action.
EXCEPTION
    WHEN OTHERS THEN RAISE NOTICE 'Error';  -- ⚠️ The error is suppressed

-- Don't use the retry without the limit.
LOOP
    BEGIN
        ...
    EXCEPTION
        WHEN OTHERS THEN CONTINUE;  -- ❌ The infinite loop
    END;
END LOOP;  -- ❌

-- Don't expose the raw message to the user.
RAISE EXCEPTION '%', SQLERRM;  -- ⚠️ The internal details

-- Don't forget the savepoint for the partial rollback.
BEGIN
    INSERT ...;  -- ❌ The failed insert aborts the whole transaction
END;  -- ❌

Common Pitfalls

Pitfall	Why It Happens	Fix
The transaction aborted	No rollback	Add the rollback
The error suppressed	The `NULL` handler	Re-raise or log
The infinite retry	No limit	Add the attempt counter
The partial work lost	No savepoint	Use the inner block
The user sees the raw error	No mapping	Map the code to the message
The deadlock not retried	No handler	Add the `serialization_failure`
The diagnostic missing	No log	Insert into the error table

Real-World Examples

1. The Basic Handler

EXCEPTION WHEN OTHERS THEN ROLLBACK; RAISE;

2. The Unique Violation

EXCEPTION WHEN unique_violation THEN UPDATE ...;

3. The SQL Server Catch

BEGIN CATCH ROLLBACK; THROW; END CATCH

4. The MySQL Handler

DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;

5. The Validation

RAISE EXCEPTION 'Invalid' USING ERRCODE = '22023';

6. The Upsert

EXCEPTION WHEN unique_violation THEN UPDATE ...;

7. The Retry

LOOP BEGIN ... EXCEPTION WHEN serialization_failure THEN ... END; END LOOP;

8. The Logging

INSERT INTO error_log (code, message) VALUES (SQLSTATE, SQLERRM);

9. The Savepoint

BEGIN INSERT ...; EXCEPTION WHEN unique_violation THEN ...; END;

10. The Complete Pattern

BEGIN ... EXCEPTION WHEN OTHERS THEN ROLLBACK; RAISE; END;

Visual

The Exception Flow

┌────────────────────────────────────────────────────────────┐
│  THE EXCEPTION FLOW                                        │
│                                                            │
│  BEGIN                                                     │
│  ┌──────────────────────────────────────────────────┐     │
│  │  The operation runs.                              │     │
│  │    ↓                                              │     │
│  │  An error occurs.                                 │     │
│  │    ↓                                              │     │
│  │  The exception is raised.                         │     │
│  └────────────────────────┬─────────────────────────┘     │
│                           │                                │
│                           v                                │
│  EXCEPTION                                                 │
│  ┌──────────────────────────────────────────────────┐     │
│  │  WHEN unique_violation THEN  → the handler        │     │
│  │  WHEN foreign_key_violation THEN → the handler    │     │
│  │  WHEN OTHERS THEN  → the catch-all                │     │
│  └────────────────────────┬─────────────────────────┘     │
│                           │                                │
│                           v                                │
│  ┌──────────────────────────────────────────────────┐     │
│  │  The handler runs.                                │     │
│  │    ↓                                              │     │
│  │  The ROLLBACK or the RAISE.                       │     │
│  └──────────────────────────────────────────────────┘     │
│                                                            │
│  The exception is the signal. The handler is the response. │
└────────────────────────────────────────────────────────────┘

The Retry Pattern

┌────────────────────────────────────────────────────────────┐
│  THE RETRY PATTERN                                         │
│                                                            │
│  attempt = 0                                               │
│  LOOP                                                      │
│    attempt = attempt + 1                                   │
│    BEGIN                                                   │
│      ┌──────────────────────────────────────────┐         │
│      │  The operation.                           │         │
│      │    ↓                                      │         │
│      │  Success: RETURN.                         │         │
│      │    ↓                                      │         │
│      │  serialization_failure: retry.            │         │
│      └──────────────────────────────────────────┘         │
│    EXCEPTION                                               │
│      WHEN serialization_failure THEN                       │
│        IF attempt >= max_retries THEN RAISE; END IF;       │
│        PERFORM pg_sleep(0.1 * attempt);                    │
│    END;                                                    │
│  END LOOP;                                                 │
│                                                            │
│  The transient error is the signal. The retry is the fix.  │
└────────────────────────────────────────────────────────────┘

The Savepoint Pattern

┌────────────────────────────────────────────────────────────┐
│  THE SAVEPOINT PATTERN                                     │
│                                                            │
│  FOR item IN ... LOOP                                      │
│    BEGIN                                                   │
│      ┌──────────────────────────────────────────┐         │
│      │  INSERT INTO items ...                    │         │
│      │    ↓                                      │         │
│      │  Success: the loop continues.             │         │
│      │    ↓                                      │         │
│      │  unique_violation: the inner block        │         │
│      │  rolls back, the loop continues.          │         │
│      └──────────────────────────────────────────┘         │
│    EXCEPTION                                               │
│      WHEN unique_violation THEN                            │
│        RAISE NOTICE 'Skipping duplicate';                  │
│    END;                                                    │
│  END LOOP;                                                 │
│                                                            │
│  The inner block is the savepoint. The partial work is     │
│  undone, and the loop continues.                           │
└────────────────────────────────────────────────────────────┘

The Error Log

┌────────────────────────────────────────────────────────────┐
│  THE ERROR LOG                                             │
│                                                            │
│  error_log                                                 │
│  ┌──────────────────────────────────────────────────┐     │
│  │  procedure_name | error_code | error_message      │     │
│  │  ---------------+------------+------------------  │     │
│  │  process_order  | 23505      | duplicate key      │     │
│  │  transfer_funds | 40001      | serialization      │     │
│  │  create_user    | 22023      | invalid email      │     │
│  └──────────────────────────────────────────────────┘     │
│                                                            │
│  The log is the audit. The handler writes the record.      │
└────────────────────────────────────────────────────────────┘

Summary

Item	Value
`EXCEPTION`	The PostgreSQL block
`TRY`/`CATCH`	The SQL Server block
`DECLARE ... HANDLER`	The MySQL block
`WHEN OTHERS`	The catch-all
`unique_violation`	The duplicate key
`foreign_key_violation`	The missing reference
`serialization_failure`	The serialization conflict
`RAISE`	The re-raise
`SQLSTATE`	The error code
`SQLERRM`	The error message

Key takeaways:

The exception block is the error boundary. The BEGIN ... EXCEPTION in the PostgreSQL, the TRY/CATCH in the SQL Server, and the DECLARE ... HANDLER in the MySQL. The block catches the error, the handler runs the recovery, and the block continues or aborts.
The WHEN OTHERS is the catch-all. The specific handlers catch the known errors, and the WHEN OTHERS catches the rest. The order matters: the specific handlers come first.
The rollback is the transaction control. The handler that catches the error must decide whether to roll back. The ROLLBACK in the handler is the common response, and the RAISE re-raises after.
The SQLSTATE and the SQLERRM are the diagnostics. The SQLSTATE is the code, and the SQLERRM is the message. The handler logs the two and maps the code to the user-facing message.
The retry is the transient-error response. The serialization_failure and the deadlock_detected are the transient. The loop with the sleep and the attempt limit is the pattern.
The savepoint is the partial rollback. The inner BEGIN ... EXCEPTION block is the savepoint. The failed statement is rolled back, and the outer transaction continues.
The error log is the audit. The handler inserts the diagnostic into the table. The log is the record of the failure, and the application uses it for the monitoring.
The user-facing message is the mapping. The raw error message is not the user-facing message. The handler maps the error code to the friendly message, and the application displays it.

Remember: The error handling in the procedural SQL is the boundary between the database failure and the application response. The exception block catches the error, the handler runs the recovery, and the transaction is the unit of the atomicity. The specific handlers catch the known errors, the WHEN OTHERS catches the rest, and the RAISE re-raises after the handling. The result is a procedure that handles the failure, logs the diagnostic, and returns the structured result to the application.

Stop using slow, ad-bloated tool sites! 🤮

🔎 Search “KandZ Tools” on Google to use many professional utilities for free.

KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)

⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free

🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!