Logging messages on specific exceptions

One of the best uses of catching multiple specific exception is to distinctly handle and log unique error message that help you understand exactly why an exception occurred. Let's apply this in a scenario where both error conditions are possible. We'll discuss after the exercise why it capture the specific message it did.

This exercise is part of the course

Transactions and Error Handling in PostgreSQL

View Course

Exercise instructions

Build an exception handler for a not_null_violation.
Insert "failed to insert" as the msg, Glucose can not be null. as the detail into the errors table if a not_null_violation occurs.

Hands-on interactive exercise

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

-- Make a DO function
DO $$
-- Open a transaction block
___
    INSERT INTO patients (a1c, glucose, fasting) values (20, null, TRUE);
-- Catch an Exception                                                               
EXCEPTION
	-- Make it catch check_violation exception types
    WHEN check_violation THEN
    	-- Insert the proper msg and detail
       INSERT INTO errors (msg, detail)
       VALUES ('failed to insert', 'A1C is higher than clinically accepted norms.');
    -- Make it catch not_null_violation exception types
    ___ ___ ___
    	-- Insert the proper msg and detail
       INSERT INTO errors (msg, detail) 
       ___ (___, ___);
END$$;
                                                                     
-- Select all the errors recorded
SELECT * FROM errors;

Edit and Run Code