Spring Deals - hours only!Up to 70% off on all courses and bundles.-Close
Introduction
Syntax of PL/pgSQL
Function parameters
Handling exceptions
25. Handling exceptions
Summary

Instruction

Great! Let's now move on to exception handling. You can catch errors/exceptions and recover from them by using a BEGIN block with an EXCEPTION clause.

If no error occurs, the EXCEPTION block will simply be ignored, and control will be passed to the next statement after END. In case an error occurs within the statements, control will pass to the EXCEPTION block, where the exception list will be searched for the first condition matching the error. The detailed list of all error codes is available in the official Postgres documentation.

The syntax is an extension of the BEGIN block syntax:

BEGIN
  RAISE NOTICE 'Department budget per employee is: %', total_budget_for_dep / num_of_employees_in_dep;
EXCEPTION
  WHEN division_by_zero THEN
    RAISE NOTICE 'Caught division_by_zero exception!';
END;

In the example above, if the num_of_employees_in_dep is zero, the division_by_zero exception will be handled, and the following message will be printed out:

NOTICE: Caught division_by_zero exception!

Exercise

Complete the predefined template so the final script will handle the null_value_not_allowed exception and raises the following notice:

Caught null_value_not_allowed exception!

Stuck? Here's a hint!

Use the following block:

EXCEPTION
  WHEN null_value_not_allowed THEN
    <action>