How to Use RAISE NOTICE in PostgreSQL for Debugging

In PostgreSQL, use RAISE NOTICE to print informational messages during function execution or scripts. It helps you debug by showing custom messages without stopping the process.

Syntax

The basic syntax of RAISE NOTICE is:

• RAISE NOTICE 'message'; - prints a simple message.
• RAISE NOTICE 'format string', expression1, expression2, ...; - prints a formatted message with variables.

This command outputs messages to the client or server log depending on configuration.

sql

RAISE NOTICE 'This is a simple notice message';

RAISE NOTICE 'User ID: %, Name: %', user_id, user_name;

Example

This example shows a PostgreSQL function that uses RAISE NOTICE to display messages during execution. It prints a welcome message and shows the input parameter.

sql

CREATE OR REPLACE FUNCTION greet_user(user_name text) RETURNS void AS $$
BEGIN
  RAISE NOTICE 'Hello, %!', user_name;
  RAISE NOTICE 'Function greet_user executed successfully.';
END;
$$ LANGUAGE plpgsql;

-- Call the function
SELECT greet_user('Alice');

Output

NOTICE:  Hello, Alice!
NOTICE:  Function greet_user executed successfully.

Common Pitfalls

Common mistakes when using RAISE NOTICE include:

• Forgetting to use % placeholders when including variables in the message.
• Using RAISE NOTICE outside of PL/pgSQL blocks where it is not allowed.
• Expecting RAISE NOTICE to stop execution or handle errors (it only prints messages).

Always ensure your format string matches the number of variables.

sql

/* Wrong: Missing % placeholder for variable */
RAISE NOTICE 'User ID is ' || user_id; -- This concatenation works but is less flexible

/* Correct: Using % placeholder */
RAISE NOTICE 'User ID is %', user_id;

Quick Reference

Feature	Description
RAISE NOTICE 'message';	Prints a simple informational message.
RAISE NOTICE 'format %', var;	Prints a formatted message with variables.
Used inside PL/pgSQL functions	Works only in procedural code blocks.
Does not stop execution	Only outputs messages, no error handling.
Output destination	Messages appear in client console or server log.

Key Takeaways

Use RAISE NOTICE to print messages for debugging inside PL/pgSQL functions.

Include % placeholders to insert variable values in messages.

RAISE NOTICE messages do not stop function execution or handle errors.

It helps track function flow and variable values during runtime.

Messages appear in the client console or server logs depending on settings.