Search code examples
postgresqlunit-testingpgtap

pgTAP - pass test if insertion fails

I want to test the following constraint I made on a postgres-schema:

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

Therefore I've planed to write a test that passes, when a insertion with a wrong status fails. For example, this would fail:

INSERT INTO cmdb (name, beschreibung, status) VALUES ('new device', 'descriptive details', 'online')

because the online-string is missing the suffix (e.g. :sometimes). I've decided to use pgTAP for this, but I'm struggeling with the syntax. This is what I already have come up with:

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;

But I am getting the following error when executing with pg_prove:

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

Maybe it's a minor syntax error of mine or I have misunterstood the usage of the ok()-function. Or maybe there is a different way to test insertions.

Thanks your time!

Solution

  • Instead of using ok() and nesting my INSERT-query into and NOT EXIST, I have found a different approach with the throws_ok()-function, which checks for thrown errors.

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


PREPARE status_insert AS INSERT INTO mytable (name, beschreibung, status) VALUES ('foobar', 'barfoo', 'online');

-- testing for thrown error
SELECT throws_ok(
    'status_insert',
    '23514', -- wanted error code https://www.postgresql.org/docs/current/errcodes-appendix.html
    'new row for relation "cmdb" violates check constraint "status_format"'); -- wanted message

SELECT * FROM finish();
ROLLBACK;

    You can find further explanation on throws_ok() in the documentation: