PostgreSQL has built-in error handling mechanisms. We need to show these errors to users and report them through messages in order to maintain the normal execution of the program without interpreting it. The “RAISE NOTICE” statement is used for this purpose, allowing us to report messages to users. Let's explore how the “RAISE NOTICE” statement works in PostgreSQL.

RAISE NOTICE Statement in PostgreSQL

In order to raise an error message, the RAISE keyword is used. RAISE NOTICE is used to raise an error and that error is reported back to the user. The basic syntax of RAISE NOTICE is:

RAISE NOTICE [format]

Here format basically specifies the mirror message that is reported to the user. The format is basically a string. The format may contain a % placeholder, which will be substituted by an argument. It is necessary that the placeholders should be equal in number to the number of arguments. Otherwise, this would result in an error.

Let’s cover the topic with the help of a simple example:

DO $$ 
BEGIN 
 RAISE NOTICE 'Notice message  %', now();
END $$;

In the above query, the DO statement is used to execute asynchronous blocks. The BEGIN and END specify the code block, in between these two clauses the queries/code is written. And between them, RAISE NOTICE is used followed by the format, which is basically the message to be shown to the user. The format also contains a % placeholder. now() function gets the current time. This current time is then replaced by the % placeholder. let’s find out what its output looks like:

The output is exactly as per our expectations.

Consider another example of shopping:

DO $$ 
DECLARE
 add_to_cart_products INTEGER = 5;
BEGIN 

 add_to_cart_products = add_to_cart_products + 1;
 
 RAISE  NOTICE 'You have added another product into your cart';
  RAISE  NOTICE 'Now your cart contains % products', add_to_cart_products;
 
END $$;

In the above example, another section is added i.e. DECLARE. Under the DECLARE statement, we declare all the variables to use in the code block. So we have declared a variable ‘add_to_cart_products’ and initialized its value as 5. In the code block, the variable declared is incremented by one. This gives us the current value which is 6. Let’s have a look at its output.

You can see that the first RAISE NOTICE statement just displays the string passed to it. The second statement contains a % placeholder that is replaced by the variable value that was incremented by 1 and has a current value of 6.

Conclusion

RAISE NOTICE is used to raise an error and report a message and that error is reported back to the user. It is followed by a format that is basically the string we want to show to the user. In this post, we have seen the functioning of the RAISE NOTICE statement along with their practical example.