I want to be able to write a .sql script that will carry out an action which fails, and the script should only report failure if the action doesn't fail.
For example, given initial table:
create table tbl(x integer, y integer);
I might update this table in a migration with the following:
alter table tbl add constraint unique_tst unique (x, y);
And want to write a test script for that migration, which will be similar to the following:
insert into tbl(x, y) values
(1, 1),
(1, 1)
;
This will fail - which is expected given the constraint - but I'm not sure how to handle that failure in postgres.
Something such as:
if does not fail:
insert into tbl(x, y) values
(1, 1),
(1, 1)
;
return:
failure
But I have no idea if this exists.
Note - I cannot install any extensions for this.