RAISE for notices and exceptions in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this RAISE NOTICE statement?

Consider the following PostgreSQL code snippet:

DO $$ BEGIN RAISE NOTICE 'Hello, %!', 'world'; END $$;

What will be the output when this code runs?

PostgreSQL

DO $$ BEGIN RAISE NOTICE 'Hello, %!', 'world'; END $$;

A An error is raised and the transaction is aborted

B No output is shown; the statement runs silently

C A warning message is displayed instead of a notice

D A notice message: "Hello, world!" is displayed

💡 Hint

RAISE NOTICE is used to send informational messages without stopping execution.

The RAISE NOTICE command outputs an informational message to the client. It does not stop the transaction or raise an error.

❓ query_result

intermediate

What error does this RAISE EXCEPTION produce?

Examine this PostgreSQL code:

DO $$ BEGIN RAISE EXCEPTION 'Invalid input: %', 42; END $$;

What happens when this code runs?

PostgreSQL

DO $$ BEGIN RAISE EXCEPTION 'Invalid input: %', 42; END $$;

A No output; the code runs successfully

B A notice message with text: "Invalid input: 42"

C A runtime error with message: "Invalid input: 42" and transaction abort

D A syntax error due to incorrect RAISE syntax

💡 Hint

RAISE EXCEPTION stops execution and raises an error.

RAISE EXCEPTION raises an error with the given message and aborts the current transaction or block.

📝 Syntax

advanced

Which option causes a syntax error in RAISE statement?

Identify which RAISE statement is syntactically incorrect in PostgreSQL:

A RAISE EXCEPTION 'Error: %' val;

B RAISE WARNING 'Warning: %', val;

C RAISE NOTICE 'Value is %', val;

D RAISE INFO 'Info message';

💡 Hint

Check the placement of commas and parameters in RAISE syntax.

Option A is missing a comma between the format string and the parameter, causing a syntax error.

🧠 Conceptual

advanced

What is the effect of RAISE EXCEPTION inside a transaction block?

If a RAISE EXCEPTION is executed inside a transaction block in PostgreSQL, what happens?

A The exception is ignored if inside a transaction

B The current transaction is aborted and rolled back

C A warning is issued but transaction continues

D Only the current statement is skipped, transaction continues

💡 Hint

Think about how exceptions affect transactions in databases.

RAISE EXCEPTION aborts the current transaction and rolls back all changes made in it.

🔧 Debug

expert

Why does this RAISE NOTICE not display the variable value?

Given this PostgreSQL code:

DO $$ DECLARE val integer := 10; BEGIN RAISE NOTICE 'Value is %'; END $$;

Why does it not show the value of 'val' in the notice?

PostgreSQL

DO $$ DECLARE val integer := 10; BEGIN RAISE NOTICE 'Value is %'; END $$;

A The variable 'val' is not passed as an argument to RAISE NOTICE

B The variable 'val' is out of scope inside the block

C RAISE NOTICE cannot display variables

D The percent sign '%' is not allowed in RAISE NOTICE

💡 Hint

Check how placeholders and variables are used in RAISE statements.

The placeholder '%' requires a corresponding argument after the format string to display the variable value.

Practice

1. What does the RAISE NOTICE statement do in PostgreSQL?

easy

A. It silently logs a message without showing it to the user.

B. It stops the code execution and returns an error.

C. It sends a message to the client but continues running the code.

D. It restarts the current transaction automatically.

Solution

1. Step 1: Understand RAISE NOTICE behavior

   RAISE NOTICE sends an informational message to the client but does not stop the execution.

2. Step 2: Compare with other RAISE levels

   Unlike EXCEPTION, which stops execution, NOTICE only shows a message and continues.

3. Final Answer:

   It sends a message to the client but continues running the code. -> Option C

4. Quick Check:

   RAISE NOTICE = message without stopping [OK]

Hint: NOTICE shows messages but never stops code [OK]

Common Mistakes:

• Confusing NOTICE with EXCEPTION stopping code
• Thinking NOTICE hides messages
• Assuming NOTICE restarts transactions

2. Which of the following is the correct syntax to raise a warning message in a PostgreSQL function?

easy

A. RAISE WARNING 'This is a warning';

B. RAISE WARN 'This is a warning';

C. RAISE WARNNING 'This is a warning';

D. RAISE WARN_MSG 'This is a warning';

Solution

1. Step 1: Recall correct RAISE syntax for warnings

   The correct keyword is WARNING, not WARN or other variants.

2. Step 2: Validate syntax correctness

   RAISE WARNING 'message'; is the valid syntax to raise a warning message.

3. Final Answer:

   RAISE WARNING 'This is a warning'; -> Option A

4. Quick Check:

   RAISE WARNING = correct syntax [OK]

Hint: Use full word WARNING, not abbreviations [OK]

Common Mistakes:

• Using 'WARN' instead of 'WARNING'
• Misspelling WARNING as WARNNING
• Inventing unsupported keywords like WARN_MSG

3. Consider this PostgreSQL PL/pgSQL block:

DO $$
BEGIN
  RAISE NOTICE 'Step 1';
  RAISE WARNING 'Step 2';
  RAISE EXCEPTION 'Step 3';
  RAISE NOTICE 'Step 4';
END $$;

What will be the output?

medium

A. Step 1 and Step 4 notices print, but warning and exception are ignored.

B. All four messages print: Step 1, Step 2, Step 3, Step 4.

C. Only Step 3 error message is shown, others are ignored.

D. Step 1 (notice), Step 2 (warning), then error 'Step 3', and stops before Step 4.

Solution

1. Step 1: Trace the RAISE statements in order

   RAISE NOTICE 'Step 1' prints a message and continues.

2. Step 2: Understand RAISE WARNING and EXCEPTION effects

   RAISE WARNING 'Step 2' prints a warning and continues. RAISE EXCEPTION 'Step 3' raises an error and stops execution immediately.

3. Step 3: Check if Step 4 runs

   Since EXCEPTION stops execution, RAISE NOTICE 'Step 4' does not run.

4. Final Answer:

   Step 1 (notice), Step 2 (warning), then error 'Step 3', and stops before Step 4. -> Option D

5. Quick Check:

   EXCEPTION stops code after Step 3 [OK]

Hint: EXCEPTION stops code; notices/warnings do not [OK]

Common Mistakes:

• Assuming all messages print despite EXCEPTION
• Thinking WARNING stops execution
• Ignoring that EXCEPTION halts code immediately

4. You wrote this code inside a PostgreSQL function:

RAISE EXCEPTION 'Error: %', message;

But it causes an error: "ERROR: column "message" does not exist". What is the likely problem?

medium

A. The variable 'message' is not declared or out of scope.

B. RAISE EXCEPTION cannot use placeholders like '%'.

C. You must use RAISE WARNING instead of EXCEPTION for variables.

D. The syntax requires double quotes around 'message'.

Solution

1. Step 1: Understand the error message

   The error says column "message" does not exist, meaning 'message' is treated as a column or variable but is undefined.

2. Step 2: Check variable declaration and scope

   In PL/pgSQL, variables must be declared before use. If 'message' is not declared or out of scope, this error occurs.

3. Final Answer:

   The variable 'message' is not declared or out of scope. -> Option A

4. Quick Check:

   Undefined variable causes this error [OK]

Hint: Declare variables before using in RAISE [OK]

Common Mistakes:

• Thinking placeholders '%' are invalid
• Using RAISE WARNING instead of EXCEPTION for variables
• Adding quotes around variable names incorrectly

5. You want to write a PostgreSQL function that checks if a user age is below 18 and raises an exception with a custom message including the age. Which code snippet correctly does this?

hard

A. IF age < 18 THEN RAISE EXCEPTION 'User age % is too young' || age; END IF;

B. IF age < 18 THEN RAISE EXCEPTION 'User age % is too young', age; END IF;

C. IF age < 18 THEN RAISE EXCEPTION MESSAGE = 'User age ' || age || ' is too young'; END IF;

D. IF age < 18 THEN RAISE EXCEPTION 'User age % is too young', 'age'; END IF;

Solution

1. Step 1: Understand RAISE EXCEPTION with placeholders

   RAISE EXCEPTION supports printf-style placeholders like % and variables as arguments.

2. Step 2: Check each option's correctness

   IF age < 18 THEN RAISE EXCEPTION 'User age % is too young', age; END IF; correctly uses the % placeholder followed by the variable age as an argument.
   IF age < 18 THEN RAISE EXCEPTION 'User age % is too young' || age; END IF; incorrectly concatenates the variable to the format string, resulting in an unreplaced % and a runtime format error.
   IF age < 18 THEN RAISE EXCEPTION MESSAGE = 'User age ' || age || ' is too young'; END IF; omits the required USING keyword before MESSAGE=, causing a syntax error.
   IF age < 18 THEN RAISE EXCEPTION 'User age % is too young', 'age'; END IF; passes the string literal 'age' instead of the variable.

3. Final Answer:

   IF age < 18 THEN RAISE EXCEPTION 'User age % is too young', age; END IF; -> Option B

4. Quick Check:

   Use % and variable for dynamic messages [OK]

Hint: Use % placeholder with variable, not string 'age' [OK]

Common Mistakes:

• Concatenating strings instead of using placeholders
• Passing variable name as string instead of variable
• Using incorrect RAISE EXCEPTION syntax