Open In App
Related Articles

PostgreSQL – Errors and Messages

Improve Article
Save Article
Like Article

In this article, we will look into the Errors in that are inbuilt in PostgreSQL and the process of raising an error in PostgreSQL through RAISE statement and to use the ASSERT statement to insert debugging checks into PL/pgSQL blocks.

To raise an error message user can implement the RAISE statement as follows:

Syntax: RAISE level format;

Let’s explore into the raise statement a bit more. Following the RAISE statement is the level option that specifies the error severity. PostgreSQL provides the following levels:

  • LOG
  • INFO

If users don’t specify the level, by default, the RAISE statement will use the EXCEPTION level that raises an error and stops the current transaction. We will discuss the RAISE EXCEPTION later in the next section.

The format is a string that specifies the message. The format uses percentage ( %) placeholders that will be substituted by the next arguments. The number of placeholders must match the number of arguments, otherwise, PostgreSQL will report the following error message:

[Err] ERROR:  too many parameters specified for RAISE


The following example illustrates the RAISE statement that reports different messages at the current time.

DO $$ 
  RAISE INFO 'information message %', now() ;
  RAISE LOG 'log message %', now();
  RAISE DEBUG 'debug message %', now();
  RAISE WARNING 'warning message %', now();
  RAISE NOTICE 'notice message %', now();
END $$;


Note: Not all messages are reported back to the client, only INFO, WARNING, and NOTICE level messages are reported to the client. This is controlled by the client_min_messages and log_min_messages configuration parameters.

Raising Errors:

To raise errors, you use the EXCEPTION level after the RAISE statement. Note that the RAISE statement uses the EXCEPTION level by default. Besides raising an error, you can add more detailed information by using the following clause with the RAISE statement:

USING option = expression

The options can be any one of the below:

  • MESSAGE: set error message text
  • HINT: provide the hint message so that the root cause of the error is easier to be discovered.
  • DETAIL:  give detailed information about the error.
  • ERRCODE: identify the error code, which can be either by condition name or directly five-character SQLSTATE code.

Example 1:

DO $$ 
  email varchar(255) := '';
  -- check email for duplicate
  -- ...
  -- report duplicate email
  RAISE EXCEPTION 'Duplicate email: %', email 
        USING HINT = 'Check the email again';
END $$;


Example 2:

The following examples illustrate how to raise an SQLSTATE and its corresponding condition:

DO $$ 
END $$;
DO $$ 
    RAISE invalid_regular_expression;
END $$;


Whether you're preparing for your first job interview or aiming to upskill in this ever-evolving tech landscape, GeeksforGeeks Courses are your key to success. We provide top-quality content at affordable prices, all geared towards accelerating your growth in a time-bound manner. Join the millions we've already empowered, and we're here to do the same for you. Don't miss out - check it out now!

Last Updated : 28 Aug, 2020
Like Article
Save Article
Similar Reads