ASSERT in SQL

A tripwire for invariants: fail loudly the moment the impossible happens.

-- ASSERT is a tripwire for things that must always hold.
DO $$
DECLARE
    order_count BIGINT;
BEGIN
    SELECT COUNT(*)
    INTO order_count
    FROM orders;
    ASSERT order_count > 0, 'fixture must ship with orders';
    RAISE NOTICE 'invariant holds: % orders', order_count;
END;
$$;

How it works

ASSERT order_count > 0, '...' raises if the condition is false.
The message names the broken assumption for whoever reads the log.
Asserts can be disabled globally, so they guard invariants, not input.

Keywords and builtins used here

BEGIN
BIGINT
COUNT
DECLARE
DO
END
FROM
INTO
SELECT

The run, in numbers

Lines: 12
Characters to type: 264
Tokens: 37
Three-star pace: 70 tpm

At the three-star pace of 70 tokens a minute, this run takes about 32 seconds.

Type this snippet

Step 2 of 4 in Errors & triggers, step 18 of 23 in PostgreSQL Stored Procedures.

← Previous Next →