Specific exception handling and messages

1. Specific exception handling and messages

So far, we've just been using EXCEPTION WHEN others THEN to capture exceptions of any type; however, as a rule, code should catch an exception only when it knows what to do with it. For example, reporting an error with proper context, retrying with a counter, or automatically transform the data and retry, etc.

2. Catching a specific type of exception

To capture a specific type of exception, we use the same setup up to the EXCEPTION WHEN section, then as you can see in this example, we capture a check_constraint, such as a value not being less than 0, then continue as we have before. Any exception that might occur that is not of the check_violation type will raise a full exception.

3. Output of our exception handler

Here we can see the results of our prior statement. A nice INFO statement explaining the issue, and our expected insert into the errors table as well. Now let's look at some common exception types we might want to catch.

4. Common types of exception conditions

You might be wondering what types of exceptions you can catch? Well, PostgreSQL documentation provides a very exhaustive list linked in the citation here. Some common ones in my code are unique_violation for when we try to insert the same value multiple times in a unique column. The not_null_violation when we try to add a null value into a field that does not allow nulls. A check_violation, as you saw in the previous example, and the division_by_zero error.

5. Catching multiple exceptions

6. Catching multiple exception types individually

Here we are updating our inventory table, which has a check constraint on the quantity of greater than 0 and a not-null constraint on the cost column. The record we are updating for oatmeal dark chocolate cookies has a quantity of 7 before us executing this statement. Next, we are wrapping it like we did our prior example with the EXCEPTION WHEN check_violation; however, we followed that with another WHEN not_null violation. Both of our exception handlers record messages specific to their exact error condition now. In this example, only the second exception handler for the not_null_violation triggers as the quantity will be at one after the reduction, but the cost will be null. Finally, we close out our function.

7. Catching multiple exceptions output

We can see in our output that the second exception handler for the not_null_violation caught the error we triggered. Additionally, looking at the rows in the errors table, we can see the new row for this error. Capturing multiple errors types like this enables you to create targeted messages focused on each message.

8. Time to apply it!

Time to capture some specific exceptions!