Some Basic Unit Test Patterns In Postgres

Let's say you want to write a unit test for PostgreSQL that expects an exception. Here's one way to do it:

create table my_table (
  id int primary key,
  name text not null
);


create or replace function test.throws_exception_when_name_is_null() returns void as $$
begin

  insert into my_table values (1, null);
  exception when not_null_violation then return; --swallow the exception, because we are hoping for it. 

end $$ language plpgsql;


select test.throws_exception_when_name_is_null();

Some other common exception names:

• foreign_key_violation
• unique_violation
• check_violation

See the full list of exceptions

Let's say you want to run a timed test. Throw an exception if the test takes too long:

create or replace function a_long_running_function() returns void as $$
begin

    perform pg_sleep(2);

end $$ language plpgsql;


create or replace function test.test_timed() returns void as $$
begin

    perform a_long_running_function();

end $$ language plpgsql set statement_timeout to 1900;


select test.test_timed();