Catching exceptions

1. Catching exceptions

So far, we've been very focused on executing SQL queries and rolling back our mistakes, which is perfectly fine when none of our SQL statements can produce exceptions. What do we do when our queries cause exceptions?

2. Statements that error

Let's explore an example together. The table sales has a not-null constraint on the cost column. So this insert statement will result in an exception due to that constraint. Here is the output where you can see the ERROR level exception raised along with some details about why it caused an exception.

3. Generic exception capture

Thankfully like tryCatch in R and try/except in python, there is a construct in PostgreSQL that allows us to capture exceptions as well; however, we have to be inside of a PL/pgSQL function. PL/pgSQL is a procedural language found inside of PostgreSQL by default. Using PL/pgSQL opens up a wide array of capabilities for your use, such as loops and other control structures. It also enables us to use the EXCEPTION clause to capture exceptions! A fun side note here is that there are MANY languages that you can install as procedural languages in PostgreSQL, including PL/Python and PL/R!

4. PL/pgSQL DO commands (anonymous functions)

You can create both named and anonymous (aka unnamed or DO) functions to enable catching exceptions. We're going to use mostly anonymous functions in this lesson made by using a DO statement. The general structure for a DO function is DO followed by a double dollar ($$) quoted-string, which contains any variable declarations with the DECLARE keyword if needed, then is a transaction code block, and an optional language declaration. If you leave off the language declaration, it defaults to PL/pgSQL. DO functions do not return a value, unlike a named function, I prefer to use them when I don't care about the output of a statement just that it properly succeeds. A few examples of when I like them are INSERTS or UPDATES; however, because they do not return a value, they are not for use with SELECT statements that do not create something in the database. Another good use for them is as steps in a SQL data pipeline where my results are going into a table directly via statements like SELECT INTO or a CREATE TABLE AS.

5. Exception handling function

Now that you have seen an example of a function let's get back to exceptions. Here is our square root from our comparison slide earlier built as a proper DO function. We declare our function by starting with a DO statement then open the code string with a $$, next we select SQRT("a") inside of a transaction block; however, this time, we include an exception cause that triggers on any exception. `WHEN others` catches all possible exception types in a plpgSQL function. Typically when error handling, we want to be sure that we log the error in some way. Here I insert the error into an errors table for further investigation later with a message of "Can not take the square root of a string.". Additionally, I have a `RAISE INFO` statement that would log at the info level the same message. The pattern of catching the exception, recording it in a table, and logging it is a pretty typical exception handler. We'll look at a more power exception handler in a later chapter. NOTE: During the exercises of this chapter, we will not be using the RAISE statements as we don't have access to the logs to view them.

6. Using exception handling wisely

We should only use exception handling in SQL when we can't capture the exception accurately in another manner. The main reason for this is that a block containing an EXCEPTION clause adds significant overhead to enter and exit than a block without one. A properly caught exception has all the right context to help you understand the cause of, the purpose of, and possibly the path to remediation for the exception. If you are using Python or R to execute your SQL statements, it's often less overhead to use those language's exception handling capability if it can capture all the context you need. Don't sacrifice getting all the information you need to solve errors for some efficiency. Don't fret about efficiency until that is the problem you are solving. Until you understand your exceptions efficiency won't matter.

7. Changing data sets

We'll be working with a different data set for the remainder of the course. First, we have the patients table that tracks patients' blood sugar health. The patients table has the following columns patient_id, a1c (like a 3-month percentage indicator of blood sugar), glucose (the current blood sugar level in mg/dl), fasting (a flag that indicates if the test was after a fasting period) and created_on, which is just the date we added the record. We also have an errors table that has an error_id, state (the SQLSTATE error code of the exception), msg (the exception's primary message), detail (the text of the exceptions detail message), and context (the call stack at the time of the exception).

8. Let's practice!

I know what you're thinking... let's get to the code!

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.