pgTAP Documentation

Instead of writing procedural SQL scripts, you can collect tests into database functions and run them all at once using runtests(). This approach supports setup and teardown functions and runs each test in its own transaction.

Pattern:

1. Create a setup function that returns SETOF TEXT (using assertions).
2. Create test functions that return SETOF TEXT.
3. Call runtests() to execute them.

Example:

CREATE OR REPLACE FUNCTION setup_insert() RETURNS SETOF TEXT AS $$ 
BEGIN
    RETURN NEXT is( MAX(nick), NULL, 'Should have no users') FROM users;
    INSERT INTO users (nick) VALUES ('theory');
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION test_user() RETURNS SETOF TEXT AS $$ 
    SELECT is( nick, 'theory', 'Should have nick') FROM users;
$$ LANGUAGE sql;

-- To run:
SELECT * FROM runtests();

You can group multiple tests under a single TODO block using todo_start() and todo_end(). This is useful for marking a section of tests as work-in-progress. You can also nest these blocks, though it is generally not recommended.

If you use the todo() function inside a todo_start() block, you can specify the number of tests that the TODO applies to.

Warning: todo_end() is fatal if called without a preceding todo_start() call.

SELECT todo_start('working on this');
-- lots of code
SELECT todo_start('working on that');
-- more code
SELECT todo_end();
SELECT todo_end();

By convention, pgTAP assigns numbers to tests. You can provide an optional :description argument to most test functions. Using descriptions is highly recommended because:

1. It makes it easier to identify which test failed in your script.
2. It provides context in the TAP output (e.g., ok 4 - basic multi-variable instead of just ok 4).
3. It allows for better failure diagnostics.

When testing for the existence of tables, schemas, or functions, pgTAP uses a simple equivalence test (=) for identifiers.

Important: You should generally use lowercase strings for identifier arguments. If an object was created with double quotes to preserve mixed case (e.g., CREATE TABLE "Foo" ...), you must use the exact case in your test (e.g., SELECT has_table('Foo');). Otherwise, use lowercase (e.g., SELECT has_table('foo');).

To ensure test integrity and proper TAP output, you must manage the test lifecycle.

1. Declare a plan: Before running assertions, declare how many tests you intend to run. This prevents premature failure if a test is skipped or fails to run.

SELECT plan(42); -- Declare exactly 42 tests
-- Or calculate dynamically:
SELECT plan( COUNT(*) ) FROM foo;
-- Or if the number is unknown (not recommended):
SELECT * FROM no_plan();

2. Finalize the tests: At the end of your script, call finish() to output diagnostics and check if the number of tests run matches the plan.

SELECT * FROM finish();
-- To throw an exception if tests failed:
SELECT * FROM finish(true);

SELECT plan(1);
SELECT pass('test');
SELECT * FROM finish();

pgTAP must be installed on a host with a running PostgreSQL server; it cannot be installed remotely.

Linux (Debian/Ubuntu/Mint):

sudo apt-get install pgtap

Manual Build (Other systems):

1. Download pgTAP from PGXN.
2. Extract the zip and navigate to the folder.
3. Run the following commands:

make
make install
make installcheck

Note for Docker users: You must install pgTAP inside the Docker container.

For PostgreSQL 18 or later, you can install the extension into a custom prefix by passing the prefix argument to the install target.

Note: Only use the prefix argument with make install, not with other targets.

make install prefix=/usr/local/extras

After installation, you must update your postgresql.conf to include the new paths:

extension_control_path = '/usr/local/extras/postgresql/share:$system'
dynamic_library_path   = '/usr/local/extras/postgresql/lib:$libdir'

The pg_prove Perl program (from the TAP::Parser::SourceHandler::pgTAP CPAN distribution) is the recommended way to run batches of test scripts or xUnit functions.

Running SQL test scripts:

pg_prove -U postgres sql/*.sql

Running xUnit-style test functions: If you have defined your tests as database functions, use the --runtests flag:

pg_prove -d myapp --runtests

Useful flags:

• --verbose: See individual test descriptions.
• --help: View supported options.
• --man: View full documentation.

Since SQL is designed to operate on sets of rows, you can avoid manual loops for repetitive tests by using the VALUES command and SELECT statements. This allows you to run a single pgTAP test function against multiple inputs (like different schemas or columns) in one go.

To test if a table exists across multiple schemas:

SELECT has_table(sch, 'widgets', format('Has %I.widgets', sch))
  FROM (VALUES('amazon'), ('starbucks'), ('boeing')) F(sch);

To test that various columns are NOT NULL in a specific table across multiple schemas, use a CROSS JOIN:

SELECT col_not_null(sch, 'table1', col)
  FROM (VALUES('schema1'), ('schema1')) AS stmp (sch)
 CROSS JOIN (VALUES('col_pk'), ('col2'), ('col3')) AS ctmp (col);

To test pgTAP in a local Docker environment using the latest PostgreSQL version:

cd test
docker compose build test
# Start the postgres server in the background
docker compose up -d test
# Run regression tests (builds, installs, and runs installcheck)
docker compose exec test make install installcheck
# Run the full test suite with pg_prove
docker compose exec test run
# Shut down the container
docker compose down

To test against a specific PostgreSQL version, set the pgtag environment variable before running the commands:

export pgtag=12-alpine

Common installation errors and their solutions:

• Makefile: line 8: Need an operator: Use GNU make (gmake) instead of make.
• make: pg_config: Command not found or pgTAP requires PostgreSQL 9.1 or later: Ensure pg_config is in your PATH. If using an RPM-based system, install the -devel package. You can manually point to the config tool:

  env PG_CONFIG=/path/to/pg_config make && make install && make installcheck

• Fallback method: Copy the distribution to the contrib/ subdirectory of the PostgreSQL source tree and run:

  env NO_PGXS=1 make && make install && make installcheck

• ERROR: must be owner of database regression: Run the test suite as a superuser:

  make installcheck PGUSER=postgres

• ERROR: Missing extensions required for testing: citext ltree: Install the PostgreSQL contrib modules.