PostgreSQL – Errors and Messages
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:
- DEBUG
- LOG
- NOTICE
- INFO
- WARNING
- EXCEPTION
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
Example:
The following example illustrates the RAISE statement that reports different messages at the current time.
DO $$ BEGIN 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 $$;
Output:
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 $$ DECLARE email varchar(255) := 'raju@w3wiki.net'; BEGIN -- check email for duplicate -- ... -- report duplicate email RAISE EXCEPTION 'Duplicate email: %', email USING HINT = 'Check the email again'; END $$;
Output:
Example 2:
The following examples illustrate how to raise an SQLSTATE and its corresponding condition:
DO $$ BEGIN --... RAISE SQLSTATE '2210B'; END $$; DO $$ BEGIN --... RAISE invalid_regular_expression; END $$;
Output:
Contact Us