PostgreSQL - Assert

In PostgreSQL, Assert statement is primarily used with PL/pgSQL functions or procedures to execute conditions during runtime.

The usage of ASSERT statement specifically uses in debugging, data constraints, and business rule. When an assertion fail, PostgreSQL raises an exception that can be caught and handles within PL/pgSQL block.

Syntax

Following is the syntax of PostgreSQL Assert statement −

ASSERT condition [message];

Example of ASSERT Statement

Here, we provide some of basic example to understand its practical usage.

Example 1

In this example, we write the query to check whether a number is positive.

DO $$ 
BEGIN 
  ASSERT 10 > 0, 'Number is not positive'; 
  RAISE NOTICE 'Number is positive'; 
END $$;

The above query generate the following result −

Example 2

Here, we show the example to validate division by zero.

DO $$ 
DECLARE x INT := 20;
BEGIN 
  ASSERT x <> 0, 'Cannot divide by zero'; 
  RAISE NOTICE 'Valid number for division';
END $$;

The above query obtained the following result −

Example 3

This is the third example where we illustrate the person age contrainst by generating condition.

DO $$ 
DECLARE age INT := 20;
BEGIN 
  ASSERT age >= 18, 'Person is under age'; 
  RAISE NOTICE 'Person is an adult';
END $$;

The above query obtained the following result −

Example 4

A function calculates the total salary of employees in a department. You need to make sure the department ID given to the function is correct.

CREATE OR REPLACE FUNCTION cal_total_salary(department_id INT)
RETURN NUMERIC AS $$
DECLARE
       total_salary NUMERIC :=0;
BEGIN
     ASSERT EXISTS (SELECT 1 FROM EMPLOYEES WHERE employees.department_id = cal_total_salary.department_id);
     SELECT SUM(salary) INTO total_salary FROM employees WHERE employees.department_id - cal_total_salary.department_id);
     RETURN total_salary;
end;
$$ LANGUAGE plpgsql;
      
SELECT cal_total_salary(5);

The above query obtained the following result −

ERROR: assertion failed
CONTEXT: PL/pgSQL function cal_total_salary(integer) line 5 at ASSERT 

SQL state: P8884

Therefore, you can use the ASSERT statement when there is a requirement to check conditions in functions or transactions. It helps to identify the mistakes that are used in the existing query.