Exception handling (BEGIN-EXCEPTION-END) in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this exception block?

Consider the following PL/pgSQL block. What will be the output after execution?

PostgreSQL

DO $$
DECLARE
  result TEXT := '';
BEGIN
  BEGIN
    RAISE EXCEPTION 'Error occurred';
  EXCEPTION
    WHEN OTHERS THEN
      result := 'Exception caught';
  END;
  RAISE NOTICE '%', result;
END $$;

A Exception caught

B Error occurred

C No output

D Syntax error

💡 Hint

Look at how the inner BEGIN-EXCEPTION-END block handles the error.

The inner block raises an exception, which is caught by the EXCEPTION clause, setting result to 'Exception caught'. The outer block then raises a notice with that value.

📝 Syntax

intermediate

Identify the syntax error in this exception block

Which option correctly identifies the syntax error in this PL/pgSQL code snippet?

PostgreSQL

DO $$
BEGIN
  BEGIN
    RAISE EXCEPTION 'Oops';
  EXCEPTION
    WHEN OTHERS THEN
      RAISE NOTICE 'Caught';
  END;
END $$;

A Missing THEN keyword after WHEN OTHERS

B Missing END for inner BEGIN block

C RAISE NOTICE cannot be used inside EXCEPTION

D Missing semicolon after RAISE EXCEPTION

💡 Hint

Check the syntax of the EXCEPTION WHEN clause.

In PL/pgSQL, the WHEN clause must be followed by THEN before the handler code.

❓ optimization

advanced

Optimize exception handling to avoid unnecessary overhead

Which option shows the best practice to avoid performance overhead when no exception is expected?

PostgreSQL

DO $$
BEGIN
  -- Some code that rarely raises exceptions
  PERFORM some_function();
EXCEPTION
  WHEN OTHERS THEN
    RAISE NOTICE 'Exception caught';
END $$;

A Use nested BEGIN blocks with EXCEPTION inside the main block

B Remove the EXCEPTION block entirely if exceptions are rare and can be handled outside

C Replace WHEN OTHERS with WHEN SQLSTATE '23505' THEN

D Keep the EXCEPTION block as is to always catch errors

💡 Hint

Exception handling adds overhead even if no error occurs.

If exceptions are rare and can be handled at a higher level, removing the EXCEPTION block improves performance.

🔧 Debug

advanced

Does this exception block catch the error?

Given this PL/pgSQL block, which statement about the exception handling is correct?

PostgreSQL

DO $$
BEGIN
  PERFORM 1/0;
EXCEPTION
  WHEN division_by_zero THEN
    RAISE NOTICE 'Division by zero caught';
END $$;

A The exception name 'division_by_zero' is incorrect; it should be 'division_by_zero_error'

B The exception is not caught because the error is raised outside a BEGIN block

C The exception name should be SQLSTATE code '22012' instead of the name

D The exception name is correct and will catch the error

💡 Hint

Check the exact exception name or SQLSTATE code for division by zero in PostgreSQL.

The exception name 'division_by_zero' is a predefined condition name corresponding to SQLSTATE '22012' and will correctly catch the division by zero error.

🧠 Conceptual

expert

What happens when an exception is re-raised inside an EXCEPTION block?

Consider this PL/pgSQL block. What will be the final output?

PostgreSQL

DO $$
BEGIN
  BEGIN
    RAISE EXCEPTION 'Initial error';
  EXCEPTION
    WHEN OTHERS THEN
      RAISE NOTICE 'Caught error';
      RAISE;
  END;
EXCEPTION
  WHEN OTHERS THEN
    RAISE NOTICE 'Outer handler caught error';
END $$;

A Only 'Outer handler caught error' notice will be shown

B Only 'Caught error' notice will be shown

C The output will be two notices: 'Caught error' and 'Outer handler caught error'

D No notices will be shown because the exception is unhandled

💡 Hint

Think about how re-raising an exception propagates it to outer blocks.

The inner block catches and raises a notice, then re-raises the exception. The outer block catches it and raises another notice.

Practice

1. What is the main purpose of using a BEGIN-EXCEPTION-END block in PostgreSQL?

easy

A. To catch and handle errors during code execution

B. To speed up query execution

C. To create new tables automatically

D. To backup the database

Solution

1. Step 1: Understand the role of BEGIN-EXCEPTION-END

   This block is designed to catch errors that happen during the execution of SQL or PL/pgSQL code.

2. Step 2: Identify the purpose of error handling

   It allows the code to handle errors gracefully without stopping the entire process.

3. Final Answer:

   To catch and handle errors during code execution -> Option A

4. Quick Check:

   Error handling = A [OK]

Hint: Remember: BEGIN-EXCEPTION-END is for error catching [OK]

Common Mistakes:

• Thinking it speeds up queries
• Confusing it with table creation
• Assuming it backs up data

2. Which of the following is the correct syntax to start an exception block in PostgreSQL?

easy

A. BEGIN TRY ... EXCEPTION ... END

B. START ... EXCEPTION ... FINISH

C. BEGIN ... EXCEPTION ... END

D. TRY ... CATCH ... END

Solution

1. Step 1: Recall PostgreSQL exception block syntax

   PostgreSQL uses BEGIN ... EXCEPTION ... END to define a block with error handling.

2. Step 2: Compare options with correct syntax

   Only BEGIN ... EXCEPTION ... END matches the exact syntax used in PostgreSQL.

3. Final Answer:

   BEGIN ... EXCEPTION ... END -> Option C

4. Quick Check:

   PostgreSQL uses BEGIN-EXCEPTION-END syntax [OK]

Hint: Look for BEGIN and EXCEPTION keywords together [OK]

Common Mistakes:

• Using TRY-CATCH like other languages
• Adding extra keywords like START or FINISH
• Mixing syntax from other SQL dialects

3. What will be the output of the following PostgreSQL block?

DO $$
BEGIN
  RAISE EXCEPTION 'Error happened';
EXCEPTION
  WHEN OTHERS THEN
    RAISE NOTICE 'Caught an error';
END $$;

medium

A. The block raises an error and stops execution

B. It prints 'Caught an error' as a notice and continues

C. It prints 'Error happened' as a notice

D. Syntax error due to missing semicolon

Solution

1. Step 1: Analyze the RAISE EXCEPTION statement

   The block raises an exception with message 'Error happened'.

2. Step 2: Check the EXCEPTION block handling

   The exception handler catches all errors (WHEN OTHERS) and raises a NOTICE with 'Caught an error'.

3. Final Answer:

   It prints 'Caught an error' as a notice and continues -> Option B

4. Quick Check:

   Exception caught triggers NOTICE message [OK]

Hint: WHEN OTHERS catches all errors and can raise NOTICE [OK]

Common Mistakes:

• Assuming the error stops execution
• Confusing EXCEPTION message with NOTICE
• Thinking syntax error occurs

4. Identify the error in this PostgreSQL exception block:

BEGIN
  PERFORM 1/0;
EXCEPTION
  WHEN division_by_zero THEN
    RAISE NOTICE 'Division by zero caught';
END;

medium

A. Missing DO $$ ... $$ wrapper for anonymous block

B. Incorrect exception name; should be zero_division_error

C. RAISE NOTICE cannot be used inside EXCEPTION

D. No error; block is correct

Solution

1. Step 1: Check block context

   In PostgreSQL, standalone BEGIN-EXCEPTION-END blocks must be inside a DO block or function.

2. Step 2: Verify exception name and usage

   Exception name 'division_by_zero' is valid, and RAISE NOTICE is allowed inside EXCEPTION.

3. Step 3: Identify missing DO wrapper

   The code lacks DO $$ ... $$ to run as an anonymous block.

4. Final Answer:

   Missing DO $$ ... $$ wrapper for anonymous block -> Option A

5. Quick Check:

   Anonymous blocks need DO wrapper [OK]

Hint: Use DO $$ ... $$ for anonymous BEGIN blocks [OK]

Common Mistakes:

• Thinking exception name is wrong
• Believing RAISE NOTICE is invalid here
• Ignoring the need for DO wrapper

5. You want to write a PostgreSQL block that tries to insert a row into a table but if a unique constraint violation occurs, it should print 'Duplicate found' and continue without error. Which code snippet correctly implements this?

hard

A. BEGIN
INSERT INTO users(id) VALUES(1);
EXCEPTION
WHEN duplicate_key THEN
RAISE NOTICE 'Duplicate found';
END;

B. BEGIN
INSERT INTO users(id) VALUES(1);
EXCEPTION
WHEN unique_error THEN
RAISE NOTICE 'Duplicate found';
END;

C. BEGIN
INSERT INTO users(id) VALUES(1);
EXCEPTION
WHEN unique_constraint THEN
RAISE NOTICE 'Duplicate found';
END;

D. BEGIN
INSERT INTO users(id) VALUES(1);
EXCEPTION
WHEN unique_violation THEN
RAISE NOTICE 'Duplicate found';
END;

Solution

1. Step 1: Identify correct exception name for unique constraint

   PostgreSQL uses unique_violation to catch unique constraint errors.

2. Step 2: Check the exception block syntax

   The block catches unique_violation and raises a notice 'Duplicate found' correctly.

3. Step 3: Verify other options

   Other options use incorrect exception names that do not exist in PostgreSQL.

4. Final Answer:

   BEGIN INSERT INTO users(id) VALUES(1); EXCEPTION WHEN unique_violation THEN RAISE NOTICE 'Duplicate found'; END; -> Option D

5. Quick Check:

   Use unique_violation for unique constraint errors [OK]

Hint: Use unique_violation to catch duplicate key errors [OK]

Common Mistakes:

• Using wrong exception names
• Forgetting to handle unique_violation
• Not raising a notice or message