How to Unit Test in PostgreSQL

Galaxy Glossary

How do I write unit tests for PostgreSQL functions and queries?

Unit testing in PostgreSQL automates checks that SQL objects return expected results, guarding against regressions.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

Why should I unit test database code?

Bugs in functions, views, or triggers propagate bad data. Automated tests catch mistakes early, reduce manual QA, and enable confident refactoring.

What tools are available?

Popular choices are pgTAP (an extension offering JUnit-style assertions) and the native ASSERT statement in PL/pgSQL (PostgreSQL 14+).

pgTAP overview

Provides plan(), is(), ok(), and dozens of other assertions.Results surface as rows that CI services understand.

Using PL/pgSQL ASSERT

ASSERT throws an error when its condition is false, useful for quick checks inside anonymous blocks or test harnesses.

How do I install pgTAP?

Connect as superuser, then run CREATE EXTENSION IF NOT EXISTS pgtap;. Add it to each database you test.

How do I write a basic test case?

Wrap every test in a transaction that rolls back to leave the schema clean.Use plan() to declare the expected number of tests and finish() to summarise.

Example: testing order total calculation

The test inserts a mock order, calls the target function, and asserts the result.

How can I test data-modifying functions?

Run the function inside BEGIN/ROLLBACK or pgTAP’s savepoint helper. This isolates side effects.

How do I run tests in CI?

Spin up PostgreSQL in Docker, install pgTAP, load schema migrations, then run pg_prove or psql scripts.Fail the pipeline on any non-zero exit.

What are best practices?

Seed only the rows each test needs, keep tests independent, use factories for fixtures, and name tests descriptively for quick diagnosis.

Why How to Unit Test in PostgreSQL is important

How to Unit Test in PostgreSQL Example Usage


-- Function under test: sums the total_amount for a customer
CREATE OR REPLACE FUNCTION fn_customer_lifetime_value(p_customer_id INT)
RETURNS NUMERIC AS $$
  SELECT COALESCE(SUM(total_amount),0)
  FROM Orders
  WHERE customer_id = p_customer_id;
$$ LANGUAGE sql;

-- Test script using pgTAP
SELECT plan(1);

-- Insert fixture data
INSERT INTO Orders (id, customer_id, order_date, total_amount)
VALUES (9001, 1, CURRENT_DATE, 200.00);

SELECT is(
  fn_customer_lifetime_value(1),
  200.00,
  'fn_customer_lifetime_value should return 200 for customer 1'
);

-- Cleanup
ROLLBACK;
SELECT * FROM finish();

How to Unit Test in PostgreSQL Syntax


-- Enable pgTAP in the test database
CREATE EXTENSION IF NOT EXISTS pgtap;

-- Declare we expect 2 assertions
SELECT plan(2);

-- 1. Check the total amount of a known order
SELECT is(
  (SELECT total_amount FROM Orders WHERE id = 101),
  150.00,
  'Order #101 total should be 150.00'
);

-- 2. Validate stock after inserting an OrderItem
BEGIN;
INSERT INTO OrderItems (order_id, product_id, quantity)
VALUES (101, 5, 2);
SELECT is(
  (SELECT stock FROM Products WHERE id = 5),
  98,
  'Product 5 stock should decrement to 98'
);
ROLLBACK;

-- Summarise results
SELECT * FROM finish();

Common Mistakes

Running tests without wrapping them in a transaction. This leaves fixture rows inside production tables. Always use BEGIN/ROLLBACK or pgTAP savepoints so each test cleans up after itself.
Asserting against dynamic data like NOW() or sequences. Results vary between runs and cause flaky tests. Instead, stub values with fixed timestamps and deterministic IDs.

Frequently Asked Questions (FAQs)

Can I unit test without installing extensions?

Yes. Use PL/pgSQL ASSERT inside anonymous DO blocks, then capture failures in CI. However, assertions stop on first failure, whereas pgTAP reports all.