PostgreSQL – Assert

PostgreSQL provides the ASSERT statement for inserting important debugging checks in the PL/pgSQL code.It is an important tool for checking logical errors and may be used to identify problems associated with written code.

Syntax: assert condition [, message];  

If we analyze the above syntax:

• condition: The condition is a boolean expression that is supposed to return true. 
  If the condition returns true, the assert statement does nothing and the program continues to run.
  However, if the condition is evaluated to false or null, then an ASSERT_FAILURE exception is raised. 
  (If we encounter an error while evaluating the condition, it is reported as a normal error.)
  
• message: The message is an optional part which is an expression whose result (if not null) replaces the default error message text  “assertion failed”, should the condition fail. The message expression is only evaluated in case the assertion fails and not evaluated in the normal case where the assertion succeeds.

It is important to note that ASSERT is meant for detecting program bugs, not for reporting ordinary error conditions. In the case of reporting bugs, one must use the RAISE keyword.

First, we create a sample table using the below commands to perform examples:

CREATE TABLE employees (
  employee_id serial PRIMARY KEY,
  full_name VARCHAR NOT NULL,
  manager_id INT
);

Then we insert data into our employee table as follows:

INSERT INTO employees (
  employee_id,
  full_name,
  manager_id
)
VALUES
  (1, 'M.S Dhoni', NULL),
  (2, 'Sachin Tendulkar', 1),
  (3, 'R. Sharma', 1),
  (4, 'S. Raina', 1),
  (5, 'B. Kumar', 1),
  (6, 'Y. Singh', 2),
  (7, 'Virender Sehwag ', 2),
  (8, 'Ajinkya Rahane', 2),
  (9, 'Shikhar Dhawan', 2),
  (10, 'Mohammed Shami', 3),
  (11, 'Shreyas Iyer', 3),
  (12, 'Mayank Agarwal', 3),
  (13, 'K. L. Rahul', 3),
  (14, 'Hardik Pandya', 4),
  (15, 'Dinesh Karthik', 4),
  (16, 'Jasprit Bumrah', 7),
  (17, 'Kuldeep Yadav', 7),
  (18, 'Yuzvendra Chahal', 8),
  (19, 'Rishabh Pant', 8),
  (20, 'Sanju Samson', 8);

The table is:

Example 1:

The below query is used to check whether our table has one or more than 1 record.

do $$
declare  
 employee_count integer;
begin
 select count(*)
 into employee_count
 from employees;
 assert employee_count > 0, 'Check for Empty Employee Table';
end$$;

Output:

As the size of the table is greater than 0, the assertion is true and hence, nothing is printed as the error message.

Example 2:

The below query is used to check whether our table has one or more than 100 records.

do $$
declare  
 employee_count integer;
begin
 select count(*)
 into employee_count
 from employees;
 assert employee_count > 100, '100 employee count found';
end$$;

Output:

As the size of the table is lesser than 100, the assertion fails and hence, an error message is printed.