PostgreSQL provides an ASSERT statement to insert necessary debugging checks in the PL/pgSQL program. It is very crucial to take out the logical errors from the code in order to retain the normal functioning of the code. For this purpose, the ASSERT statement serves as a savior. It can also help us debug the errors related to the written code. Let's see how the assertion statement is useful.

How to Use ASSERT Statement in PostgreSQL?

ASSERT statement in PostgreSQL allows us to add some debugging checks to the code. Basically, through an ASSERT statement, a condition is imposed at a certain place in the code. If the code satisfies that condition, the program continues to run normally but if it fails the condition, it will throw an assertion error.

The basic syntax for the ASSERT statement is illustrated below:

ASSERT condition [, message];

The following are the main components of the above ASSERT query:

Condition

The condition in the above syntax always returns a Boolean value. If the condition returns true, the program continues to run normally, ASSERT does nothing. If the condition returns false or null, the ASSERT statement will throw an ASSERT_FAILURE error.

Message

The message part is completely optional. In case, this message is not passed into the ASSERT statement, by default the statement will consider it an “assertion failed” error message when the condition fails. But if this message is specified, the ASSERT statement will show that message.

Note: Please note that the assert statement is meant to be used for detecting bugs and logical errors in a code. It is not for reporting common and ordinary issues and error messages. To report error messages RAISE statement is used.

Disable/enable Assertion Testing

By default, the assertion configuration testing is ON. But we can disable the assertion testing if we want to this can be done if we go to the “plpgsql.check_asserts” parameter and set it to OFF. Then the ASSERT statement will stop functioning.

Let’s see how an ASSERT statement works using an example.

Example

Let's consider the table of the data of the candidates that are appearing in the entrance test of a university for higher education. The table contains 4 columns and multiple entries.

Now we will use the ASSERT statement on the table to see if the table “test_scores” has entries in it or not.

DO $$
 DECLARE 
  candidate_count integer;
 BEGIN
  SELECT COUNT(*)
  INTO candidate_count
  FROM test_scores;
  
  ASSERT candidate_count > 0,   'Candidates not found, check the test_score table';
   END$$;

In this query, we have declared a variable “candidate_count”, this variable is used to keep the count of the number of candidates. In the ASSERT statement, we are basically giving the condition that if candidate_count >0 then it would return nothing but if this condition becomes false, it will return the message “Candidates not found, check the test_score table”. In this particular case, we have many candidates on the table. So, the condition becomes true therefore the ASSERT statement returns nothing.

How will an ASSERT statement behave if the same query puts some different condition that becomes false? Let's see what will this case return.

DO $$
 DECLARE 
  candidate_count integer;
 BEGIN
  SELECT COUNT(*)
  INTO candidate_count
  FROM test_scores;
  
  ASSERT candidate_count > 20, 'Candidates are less than 20';
 END$$;

The table contains only 11 candidates so the condition will become false, in this case, the message, passed in the form of a string, has to be displayed.

What do you think will happen if we do not pass a message in case of a false condition?

As expected, it gave an assertion failed error. The same happens if the message is null.

Conclusion

PostgreSQL provides an ASSERT statement for inserting debugging checks for the program. The ASSERT statement provides a condition to the program. If the codes satisfy that condition, the program is executed normally. However, if the program does not satisfy the condition and returns false, the ASSERT statement will raise an error with the message provided by the user. If no message is provided, it will show an "assertion failed" error, by default.