ASSERT (PL/pgSQL)

Asserts a condition is true, raising ASSERT_FAILURE if not.

Category: error-handling

Syntax

ASSERT <condition> [, '<message>'];

Description

## Overview ASSERT evaluates a boolean condition and raises an ASSERT_FAILURE error if the condition is not true. It is a concise alternative to IF...RAISE EXCEPTION for invariant checks, data quality validations, and pipeline sanity checks. ## Behavior - The condition expression is evaluated via the DataFusion expression evaluator. Only `Boolean(Some(true))` passes the assertion. - If the condition is false, NULL, or any non-boolean value, the assertion fails. - On failure, an `Error::AssertionFailed` is raised with either the custom message (if provided) or the default "assertion failed" text. - The message expression (if provided) is evaluated only when the assertion fails, not on success. - ASSERT failures can be caught by an EXCEPTION handler in an enclosing block, just like any other error. ## Differences from PostgreSQL - PostgreSQL's ASSERT can be globally disabled via the `plpgsql.check_asserts` configuration parameter. DeltaForge always evaluates assertions; there is no configuration to disable them. - The error raised is `Error::AssertionFailed` rather than a SQLSTATE P0004 (assert_failure). Exception handlers should use WHEN OTHERS to catch it, or match on the specific condition name if supported.

Parameters

Name	Type	Description
`condition`		Provide a boolean expression that must evaluate to true. Any other value triggers the assertion failure.
`message`		Supply a custom error message displayed when the assertion fails.

Examples

DO $$
DECLARE
  count INT;
BEGIN
  SELECT count(*) INTO count FROM employees;
  ASSERT count > 0, 'Expected non-empty employees table';
  PRINT 'Employees:', count;
END;
$$;

DO $$
DECLARE
  total NUMERIC;
  item_count INT;
BEGIN
  SELECT sum(amount), count(*) INTO total, item_count FROM orders;
  ASSERT item_count > 0, 'No orders found';
  ASSERT total >= 0, 'Total amount cannot be negative: ' || total::TEXT;
  PRINT 'Order total:', total;
END;
$$;

DO $$
DECLARE
  src_count INT;
  tgt_count INT;
BEGIN
  SELECT count(*) INTO src_count FROM staging_data;
  SELECT count(*) INTO tgt_count FROM target_data;
  ASSERT src_count = tgt_count,
    'Row count mismatch: source=' || src_count || ' target=' || tgt_count;
  PRINT 'Counts match:', src_count;
END;
$$;

DO $$
DECLARE
  config_val TEXT;
BEGIN
  config_val := current_setting('app.mode', true);
  ASSERT config_val IS NOT NULL, 'app.mode configuration is missing';
  PRINT 'Mode:', config_val;
END;
$$;

Pitfalls

NULL conditions fail the assertion. Use COALESCE or IS NOT NULL checks if the condition expression may be NULL and that should not be treated as a failure.
Assertions cannot be disabled at runtime. Do not use ASSERT for optional debug checks that should be skippable in production. Use IF...RAISE for conditional logging instead.
The message expression is evaluated only on failure. Complex or expensive message expressions do not affect performance when the assertion passes.