PostgreSQL - Assert

PostgreSQL - Using ASSERT for Debugging

In PostgreSQL, the ASSERT statement is a debugging tool available within the PL/pgSQL procedural language. It is used to check if a condition is true during the execution of a function or a DO block. If the condition evaluates to false or NULL, an ASSERT_FAILURE exception is raised, immediately halting execution.

Important: Assertions are primarily for development and testing, not for production data validation. They can be disabled globally, so they should never be used to enforce business rules or validate user input in a production environment. Use CHECK constraints or explicit IF ... THEN RAISE EXCEPTION; blocks for that.

Enabling and Disabling Assertions

Assertions are controlled by the plpgsql.check_asserts configuration parameter. By default, it is on.

-- Check the current setting
SHOW plpgsql.check_asserts;

-- Disable assertions for the current session
SET plpgsql.check_asserts = off;

When disabled, ASSERT statements are completely ignored and have no performance overhead, which is why they are safe to leave in production code.

Syntax

The syntax for an ASSERT statement is straightforward:

ASSERT condition [ , message ];

• condition: A boolean expression that is expected to be true.
• message (optional): A custom text message to be displayed if the assertion fails. If not provided, a default message “assertion failed” is used.

Examples

Example 1: A Successful Assertion

This DO block checks if a number is positive. Since the condition 10 > 0 is true, the ASSERT passes silently and execution continues.

DO $$
BEGIN
    ASSERT 10 > 0, 'The number is not positive';
    RAISE NOTICE 'Assertion passed: The number is positive.';
END $$;

Output:

NOTICE:  Assertion passed: The number is positive.
DO

Example 2: A Failed Assertion

Here, we check if a divisor is non-zero. The assertion x <> 0 is true, so it passes. Let’s see what happens if it were false.

DO $$
DECLARE
    x INT := 0;
BEGIN
    ASSERT x <> 0, 'FATAL: Cannot divide by zero!';
    RAISE NOTICE 'This line will not be reached.';
END $$;

The assertion fails and raises an exception with our custom message:

ERROR:  FATAL: Cannot divide by zero!
CONTEXT:  PL/pgSQL function inline_code_block line 4 at ASSERT
SQL state: P0004

Example 3: Asserting Preconditions in a Function

Let’s create a function to calculate a discount, asserting that the input price is positive.

CREATE OR REPLACE FUNCTION calculate_discount(price NUMERIC, percentage INT)
RETURNS NUMERIC AS $$
BEGIN
    -- During development, ensure we never receive a negative price.
    ASSERT price > 0, 'Input price must be positive.';

    RETURN price * (percentage / 100.0);
END;
$$ LANGUAGE plpgsql;

Calling with a valid price works fine:

SELECT calculate_discount(200, 10);

-- Result:
 calculate_discount
--------------------
            20.00

Calling with an invalid price triggers the assertion:

SELECT calculate_discount(-50, 10);

-- Result:
ERROR:  Input price must be positive.
CONTEXT:  PL/pgSQL function calculate_discount(numeric,integer) line 4 at ASSERT
SQL state: P0004

Best Practices: ASSERT vs. Other Checks

Method	Use Case	Environment
ASSERT	Checking internal logic, function preconditions, and invariants.	Development & Testing
RAISE EXCEPTION	Validating parameters and user input that must be enforced.	Production
CHECK Constraint	Enforcing data integrity rules at the table level.	Production