Get Started

Multiple exception blocks

Since ROLLBACK TO and SAVEPOINT can not be used in functions with exception handlers, we have a way to emulate the same behavior though using nested blocks. These nested blocks are used to group and order the statements in the order that they depend on each other. Here you are going to insert a block of records with an exception handler which emulates a SAVEPOINT, then update a record with an exception handler. That update statement will error, and the exception handler will automatically rollback just that block.

This is a part of the course

“Transactions and Error Handling in PostgreSQL”

View Course

Exercise instructions

  • In the first block, insert the following records into patients (a1c=5.6, glucose=93, and fasting=TRUE), (6.3, 111, TRUE) (4.7, 65, TRUE) with an EXCEPTION handler that inserts 'failed to insert' as the msg into errors.
  • Open a second nested block and update the patient with the ID of 1 to have a fasting value of 'true' with exception handling that records to the errors table with a msg of 'Inserted string into boolean.'.
  • END both blocks.

Hands-on interactive exercise

Have a go at this exercise by completing this sample code.

-- Make a DO function
DO $$
-- Open a transaction block
BEGIN
    -- Open a nested block
    BEGIN
    	INSERT INTO ___ (___, ___, ___) 
        VALUES (5.6, 93, TRUE), ___, ___;
    -- Catch all exception types
    EXCEPTION WHEN others THEN
    	INSERT INTO ___ (msg) VALUES ('failed to insert');
    -- End nested block
    END;
    -- Begin the second nested block
	BEGIN
    	UPDATE patients SET ___ = '___' WHERE id=1;
    -- Catch all exception types
    EXCEPTION WHEN others THEN
        INSERT INTO errors (msg) VALUES ('Inserted ___ into ___.');
    -- End the second nested block
    END;
-- END the outer block
END;
$$ language 'plpgsql';
SELECT * FROM errors;
Edit and Run Code

This exercise is part of the course

Transactions and Error Handling in PostgreSQL

IntermediateSkill Level
5.0+
1 reviews

Ensure data consistency by learning how to use transactions and handle errors in concurrent environments.

Start Course for Free

In this chapter, you'll learn about exceptions and how to handle them.

Exercise 1: Catching exceptionsExercise 2: Writing do statementsExercise 3: Using exception handling wiselyExercise 4: Handling exceptionsExercise 5: Rollbacks, savepoints, and exceptionsExercise 6: Multiple exception blocks
Exercise 7: Understanding rollbacks and savepointsExercise 8: Specific exception handling and messagesExercise 9: Capturing specific exceptionsExercise 10: Logging messages on specific exceptionsExercise 11: Graceful exception handlingExercise 12: When to use graceful degradationExercise 13: Graceful degradation

What is DataCamp?

Learn the data skills you need online at your own pace—from non-coding essentials to data science and machine learning.

Start Learning for Free