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:
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.
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();
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.
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.
email varchar(255) := 'email@example.com';
-- check email for duplicate
-- report duplicate email
RAISE EXCEPTION 'Duplicate email: %', email
USING HINT = 'Check the email again';
The following examples illustrate how to raise an SQLSTATE and its corresponding condition:
RAISE SQLSTATE '2210B';
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!