PostgreSQL Exception Handling Using PL/pgSQL

Errors can occur while executing SQL statements due to invalid data, violations of constraints, or unexpected conditions.
PostgreSQL provides exception handling in PL/pgSQL to gracefully manage these errors.

In this article, you will learn:

• What exceptions are
• Why exception handling is important
• How exception handling works in PL/pgSQL
• Common exception examples
• Best practices for handling errors

This guide is written for beginners.

---

What Is an Exception in PostgreSQL?

An exception is an error that occurs while executing SQL or PL/pgSQL code.

Examples of errors:

• Division by zero
• Duplicate key violation
• NULL value violation
• Data type mismatch

Without handling exceptions, PostgreSQL stops execution and throws an error.

---

Why Is Exception Handling Important?

Exception handling helps:

• Prevent application crashes
• Handle invalid data gracefully
• Maintain database consistency
• Log and debug errors
• Roll back transactions when needed

Proper exception handling makes your database logic safe and reliable.

---

Exception Handling in PL/pgSQL

PostgreSQL uses the EXCEPTION block to handle errors.

Basic structure:

SQL

BEGIN

-- SQL statements

EXCEPTION

WHEN error_condition THEN

-- error handling logic

END;

Show more lines

When an error occurs inside BEGIN, control moves to the EXCEPTION block.

---

Simple Exception Handling Example

SQL

CREATE OR REPLACE FUNCTION divide_numbers(a INTEGER, b INTEGER)

RETURNS INTEGER AS $

BEGIN

RETURN a / b;

 

EXCEPTION

WHEN division_by_zero THEN

RETURN 0;

END;

$ LANGUAGE plpgsql;

Show more lines

Calling the function:

SQL

SELECT divide_numbers(10, 0);

Show more lines

Instead of failing, the function returns 0.

---

Handling UNIQUE Constraint Violation

Example table:

SQL

CREATE TABLE users (

id SERIAL PRIMARY KEY,

email TEXT UNIQUE

);

Show more lines

---

Function with Exception Handling

SQL

CREATE OR REPLACE FUNCTION insert_user(user_email TEXT)

RETURNS TEXT AS $

BEGIN

INSERT INTO users(email)

VALUES (user_email);

 

RETURN 'User inserted successfully';

 

EXCEPTION

WHEN unique_violation THEN

RETURN 'Email already exists';

END;

$ LANGUAGE plpgsql;

Show more lines

This prevents application failure when duplicate data is inserted.

---

Using WHEN OTHERS

WHEN OTHERS catches any error.

Example:

SQL

CREATE OR REPLACE FUNCTION safe_insert(user_email TEXT)

RETURNS TEXT AS $

BEGIN

INSERT INTO users(email)

VALUES (user_email);

 

RETURN 'Success';

 

EXCEPTION

WHEN OTHERS THEN

RETURN 'An error occurred';

END;

$ LANGUAGE plpgsql;

Show more lines

Use WHEN OTHERS carefully, as it hides specific errors.

---

Raising Custom Exceptions

You can raise your own errors using RAISE.

Raise an Exception

SQL

RAISE EXCEPTION 'Invalid input value';

Show more lines

---

Example with Custom Error

SQL

CREATE OR REPLACE FUNCTION check_age(age INTEGER)

RETURNS TEXT AS $

BEGIN

IF age < 0 THEN

RAISE EXCEPTION 'Age cannot be negative';

END IF;

 

RETURN 'Valid age';

END;

$ LANGUAGE plpgsql;

 

Show more lines

Calling this function with invalid input raises an error.

---

RAISE NOTICE, WARNING, and INFO

You can display messages without stopping execution.

Example:

SQL

RAISE NOTICE 'Processing started';

RAISE WARNING 'This is a warning';

RAISE INFO 'Execution completed';

Show more lines

These are useful for debugging.

---

Exception Handling in Procedures

Procedures commonly use exception handling with transaction control.

Example:

SQL

CREATE OR REPLACE PROCEDURE safe_transfer()

LANGUAGE plpgsql

AS $

BEGIN

UPDATE accounts SET balance = balance - 100 WHERE id = 1;

UPDATE accounts SET balance = balance + 100 WHERE id = 2;

 

COMMIT;

 

EXCEPTION

WHEN OTHERS THEN

ROLLBACK;

END;

$;

Show more lines

If any error occurs, the transaction is rolled back.

---

Common PostgreSQL Exception Conditions

Some commonly used exception names:

• division_by_zero
• unique_violation
• not_null_violation
• foreign_key_violation
• check_violation

Using specific exceptions is better than using WHEN OTHERS.

---

Best Practices for Exception Handling

• Handle only expected errors
• Use specific exception names
• Avoid hiding errors with WHEN OTHERS
• Log or return meaningful messages
• Use ROLLBACK in procedures when needed

Good error handling improves maintainability.

---

Summary

• Exceptions handle runtime errors
• Use EXCEPTION blocks in PL/pgSQL
• Catch specific errors when possible
• Use RAISE for custom errors
• Exception handling improves reliability

You now understand exception handling in PostgreSQL.

---

What’s Next?

Next, we will cover PostgreSQL performance tips and query optimization basics.

Next article:
PostgreSQL Performance Tuning Basics for Beginners