如何用pgTAP测试PostgreSQL约束：插入非法值时测试通过

我在PostgreSQL数据库的schema中定义了如下CHECK约束，要求status字段的'online'值必须带有后缀（如online:sometimes）：

-- status 'online' is just allowed with a suffix, e.g. 'online:sometimes'
CONSTRAINT status_format CHECK(((status)::text~*'^(online:.*|online:.*/.*)$'::text))

想要用pgTAP编写测试，验证插入不符合要求的status（如单独的online）时，会因触发约束而插入失败，测试用例通过。但编写的测试代码执行时出现错误：

ERROR:  invalid input syntax for type boolean: "NOT EXISTS (

原测试代码如下：

BEGIN; -- Test to check if the INSERT statement fails as expected
SELECT plan(1);

-- Check if the insertion operation fails
SELECT ok(
    $$NOT EXISTS (
        INSERT INTO mytable (name, beschreibung, status) VALUES ('new device', 'descriptive details', 'online')
        RETURNING id
    )$$,
    'Constraint check failed for inserting "online" status without valid time period'
);

SELECT * FROM finish();
ROLLBACK;

问题原因 #

ok()函数的第一个参数需要是布尔值表达式，但原代码把完整的SQL查询以字符串形式传入，PostgreSQL会尝试将这个字符串转换为布尔值，从而触发语法错误。

正确解决方案 #

使用pgTAP提供的throws_ok()函数，这是专门用于测试SQL语句是否抛出指定错误的工具，比NOT EXISTS更适合这类约束验证场景：

BEGIN;
SELECT plan(1);

-- 验证插入无后缀的"online"会触发CHECK约束错误
SELECT throws_ok(
    $$INSERT INTO mytable (name, beschreibung, status) VALUES ('new device', 'descriptive details', 'online')$$,
    '23514', -- CHECK约束违反的标准错误码
    '%violates check constraint "status_format"%', -- 匹配错误消息的部分内容，支持通配符
    '插入无后缀的"online"时触发CHECK约束失败'
);

SELECT * FROM finish();
ROLLBACK;

代码说明 #

1. throws_ok()参数说明：

   • 第一个参数：要执行的SQL语句（字符串形式）
   • 第二个参数：PostgreSQL的标准错误码，23514对应CHECK约束违反
   • 第三个参数：匹配错误消息的内容，使用%作为通配符，避免因错误消息的细微差异导致测试失败
   • 第四个参数：测试用例的描述文本

2. 为什么不用原思路？
   插入违反CHECK约束的记录时，PostgreSQL会直接抛出异常，不会执行RETURNING，因此NOT EXISTS的写法根本无法正常执行，会在执行INSERT时就触发错误，而不是返回布尔值。

内容的提问来源于stack exchange，提问作者hmaier