Capturing specific exceptions
Let's build a DO function that captures when glucose is set to null, and logs a message stating explicitly that Glucose can not be null.
This exercise is part of the course
Transactions and Error Handling in PostgreSQL
Exercise instructions
- Inside of the
BEGIN
block of the DO function,INSERT
intopatients
the row (a1c
=7.5
,glucose
=null
, andfasting
=TRUE
). - Add a
not_null_violation
exception that inserts"Glucose can not be null."
in the detail column of errors in case of an error.
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
INSERT INTO patients (a1c, glucose, fasting)
VALUES (7.5, ___, TRUE);
-- Catch an Exception
EXCEPTION
-- Make it catch not_null_constraint exception types
WHEN ___ ___
-- Insert the proper msg and detail
INSERT INTO errors (___, detail)
VALUES ('failed to insert', '___');
END$$;
-- Select all the errors recorded
SELECT * FROM errors;