Exception handling (BEGIN-EXCEPTION-END) in PostgreSQL - Mini Project: Build & Apply

Handling Errors in PostgreSQL with BEGIN-EXCEPTION-END

📖 Scenario: You are managing a small bookstore database. Sometimes, when adding new books, errors can happen, like trying to add a book with a duplicate ID. You want to handle these errors gracefully so the database stays safe and you get a clear message about what went wrong.

🎯 Goal: Build a simple PostgreSQL function that tries to insert a new book into the books table. Use BEGIN-EXCEPTION-END blocks to catch errors like duplicate IDs and handle them by returning a friendly message.

📋 What You'll Learn

Create a books table with columns id (integer primary key) and title (text).

Write a function called add_book that takes book_id and book_title as inputs.

Use a BEGIN-EXCEPTION-END block inside the function to try inserting the book.

Catch the unique_violation exception when a duplicate id is inserted.

Return a message indicating success or the error caught.

💡 Why This Matters

🌍 Real World

Handling errors in database operations is important to keep data safe and provide clear feedback to users or applications.

💼 Career

Database developers and administrators often write functions with exception handling to make systems robust and user-friendly.

1

Create the books table

Create a table called books with two columns: id as an integer primary key and title as text.

PostgreSQL

-- Your code here to create the books table

Need a hint?

Use CREATE TABLE with id INTEGER PRIMARY KEY and title TEXT.

2

Start the add_book function

Write the first line of a function called add_book that takes two parameters: book_id as integer and book_title as text. The function should return text.

PostgreSQL

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT
);

-- Start the add_book function definition here

Need a hint?

Use CREATE OR REPLACE FUNCTION with parameters and RETURNS TEXT. Start the BEGIN block.

3

Add the insert and exception block

Inside the BEGIN block of add_book, write an INSERT statement to add the book to books. Then add an EXCEPTION block to catch unique_violation errors and return the message 'Duplicate book ID'.

PostgreSQL

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT
);

CREATE OR REPLACE FUNCTION add_book(book_id INTEGER, book_title TEXT) RETURNS TEXT AS $$
BEGIN
    -- Insert the book here
    -- Add EXCEPTION block to catch unique_violation
END;
$$ LANGUAGE plpgsql;

Need a hint?

Use INSERT INTO inside BEGIN. Add EXCEPTION WHEN unique_violation THEN to catch duplicates.

4

Complete the function with success message

Make sure the function add_book returns 'Book added successfully' when the insert works without errors.

PostgreSQL

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT
);

CREATE OR REPLACE FUNCTION add_book(book_id INTEGER, book_title TEXT) RETURNS TEXT AS $$
BEGIN
    INSERT INTO books(id, title) VALUES (book_id, book_title);
    -- Ensure success message is returned
EXCEPTION
    WHEN unique_violation THEN
        RETURN 'Duplicate book ID';
END;
$$ LANGUAGE plpgsql;

Need a hint?

Return the success message right after the insert statement inside BEGIN.