Exception handling (BEGIN-EXCEPTION-END) in PostgreSQL

Introduction

Exception handling helps your database code catch and manage errors smoothly. It stops your program from crashing and lets you fix or report problems.

When you want to avoid stopping a batch of database commands because of one error.

When you need to log or report errors during data updates or inserts.

When you want to provide a friendly message instead of a confusing error to users.

When you want to try alternative actions if the first one fails.

When you want to clean up resources or rollback changes after an error.

Syntax

PostgreSQL

BEGIN
  -- your SQL statements here
EXCEPTION
  WHEN condition THEN
    -- error handling code here
END;

BEGIN starts a block of code.

EXCEPTION catches errors that happen inside the block.

Examples

This tries to insert a user but shows a message if the user ID already exists.

PostgreSQL

BEGIN
  INSERT INTO users(id, name) VALUES (1, 'Alice');
EXCEPTION
  WHEN unique_violation THEN
    RAISE NOTICE 'User already exists';
END;

This updates an account balance and catches any error with a general message.

PostgreSQL

BEGIN
  UPDATE accounts SET balance = balance - 100 WHERE id = 10;
EXCEPTION
  WHEN others THEN
    RAISE NOTICE 'An error occurred during update';
END;

Sample Program

This block tries to add an employee. If the ID is already taken, it shows a friendly notice instead of an error.

PostgreSQL

DO $$
BEGIN
  INSERT INTO employees(id, name) VALUES (1, 'John');
EXCEPTION
  WHEN unique_violation THEN
    RAISE NOTICE 'Employee with this ID already exists.';
END;
$$;

Important Notes

You can catch specific errors like unique_violation or use WHEN others THEN to catch all errors.

Use RAISE NOTICE to show messages without stopping the program.

Exception blocks help keep your database work safe and user-friendly.

Summary

Use BEGIN-EXCEPTION-END blocks to catch and handle errors in PostgreSQL.

This helps your database code run smoothly without crashing on errors.

You can give clear messages or take special actions when errors happen.