How to Use RAISE EXCEPTION in PostgreSQL for Error Handling

In PostgreSQL, you use RAISE EXCEPTION inside PL/pgSQL blocks to stop execution and show an error message. This helps you handle errors by providing custom messages when something goes wrong.

Syntax

The RAISE EXCEPTION statement is used inside PL/pgSQL code blocks to throw an error and stop the current operation. You can include a custom error message and optional parameters.

• RAISE EXCEPTION 'message': Throws an error with the given message.
• RAISE EXCEPTION 'message %', variable: Inserts variable values into the message using % placeholders.

sql

RAISE EXCEPTION 'error message';

-- With variable substitution
RAISE EXCEPTION 'Error: value % is invalid', some_variable;

Example

This example shows a function that raises an exception if the input number is negative. It stops execution and returns the custom error message.

sql

CREATE OR REPLACE FUNCTION check_positive(num integer) RETURNS void AS $$
BEGIN
  IF num < 0 THEN
    RAISE EXCEPTION 'Input number % is negative, must be positive', num;
  END IF;
END;
$$ LANGUAGE plpgsql;

-- Call the function with a negative number to see the exception
SELECT check_positive(-5);

Output

ERROR:  Input number -5 is negative, must be positive
CONTEXT:  PL/pgSQL function check_positive(integer) line 4 at RAISE

Common Pitfalls

Common mistakes when using RAISE EXCEPTION include:

• Using it outside PL/pgSQL blocks, which causes syntax errors.
• Not providing a message string, which is required.
• Incorrectly formatting placeholders and variables in the message.

Always ensure RAISE EXCEPTION is inside a BEGIN ... END block in a function or procedure.

sql

/* Wrong: Using RAISE EXCEPTION outside PL/pgSQL block */
-- RAISE EXCEPTION 'This will cause an error';

/* Right: Inside a function */
CREATE OR REPLACE FUNCTION example() RETURNS void AS $$
BEGIN
  RAISE EXCEPTION 'This works fine';
END;
$$ LANGUAGE plpgsql;

Quick Reference

Usage	Description
RAISE EXCEPTION 'message';	Throws an error with a custom message.
RAISE EXCEPTION 'Error: %', var;	Includes variable values in the message.
Must be inside PL/pgSQL block	Cannot be used in plain SQL queries.
Stops execution immediately	No further code runs after exception.

Key Takeaways

Use RAISE EXCEPTION inside PL/pgSQL blocks to throw errors with custom messages.

Always provide a message string; you can include variables using % placeholders.

RAISE EXCEPTION stops the current operation immediately when triggered.

Do not use RAISE EXCEPTION outside of PL/pgSQL code blocks.

Check your message formatting to avoid syntax errors.