0

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.

1 Answer 1

1
DO
$$BEGIN
   INSERT INTO tbl (x, y)
   VALUES (1, 1), (1, 1);

   RAISE EXCEPTION 'test failed, since INSERT succeeded';
EXCEPTION
   WHEN unique_violation THEN
      /* this is OK */
      NULL;
END;$$;

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.