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.