PG_PROVE(1p)

✨ pg_prove(1p) — User Contributed Perl Documentation ✨

Okay bestie, gather round, because this is the manual for pg_prove, and it's literally the tool that runs your pgTAP tests in a PostgreSQL database. No cap. 💅

---

🌸 Name 🌸

pg_prove — a command-line tool for running and harnessing pgTAP tests

---

🎀 Usage 🎀

pg_prove tests/
pg_prove --dbname template1 test*.sql
pg_prove -d testdb --runtests

---

💅 Description 💅

pg_prove is a command-line application to run one or more pgTAP tests in a PostgreSQL database. The pgTAP homepage is at https://pgtap.org/. The output of the tests is harvested and processed by TAP::Harness in order to summarize the results of the test.

pg_prove relies on psql to run the tests. The psql docs live at https://www.postgresql.org/docs/current/app-psql.html. Because of that, it supports all of the environment variables at https://www.postgresql.org/docs/current/libpq-envars.html, as well as the password file at https://www.postgresql.org/docs/current/libpq-pgpass.html and the service file at https://www.postgresql.org/docs/current/libpq-pgservice.html read by psql.

Tests can be written and run in one of two ways, as SQL scripts or as xUnit-style database functions.

---

🧪 Test Scripts 🧪

pgTAP test scripts should consist of a series of SQL statements that output TAP. Here's a simple example that assumes that the pgTAP functions have been installed in the database:

-- Start transaction and plan the tests.
BEGIN;
SELECT plan(1);

-- Run the tests.
SELECT pass( 'My test passed, w00t!' );

-- Finish the tests and clean up.
SELECT * FROM finish();
ROLLBACK;

Now run the tests by passing the list of SQL script names or the name of a test directory to pg_prove. Here's what it looks like when the pgTAP tests are run with pg_prove:

% pg_prove -U postgres tests/
tests/coltap.....ok
tests/hastap.....ok
tests/moretap....ok
tests/pg73.......ok
tests/pktap......ok
All tests successful.
Files=5, Tests=216,  1 wallclock secs ( 0.06 usr  0.02 sys +  0.08 cusr  0.07 csys =  0.23 CPU)
Result: PASS

---

🦋 xUnit Test Functions 🦋

pgTAP test functions should return a set of text, and then simply return the values returned by pgTAP functions, like so:

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

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

Once you have these functions defined in your database, you can run them with pg_prove by using the --runtests option.

% pg_prove --dbname mydb --runtests
runtests()....ok
All tests successful.
Files=1, Tests=32,  0 wallclock secs ( 0.02 usr  0.01 sys +  0.01 cusr  0.00 csys =  0.04 CPU)
Result: PASS

Be sure to pass the --schema option if your test functions are all in one schema, and the --match option if they have names that don't start with test. For example, if you have all of your test functions in the test schema and ending with test, run the tests like so:

pg_prove --dbname mydb --schema test --match 'test$'

---

✨ Options ✨

Okay so here's the whole menu of flags. Every single one, no skips. 💅

• -b, --psql-bin PSQL — Location of the psql client.
• -d, --dbname DBNAME — Database to which to connect.
• -U, --username USERNAME — User with which to connect.
• -h, --host HOST — Host to which to connect.
• -p, --port PORT — Port to which to connect.
• -P, --pset OPTION=VALUE — Set psql key/value printing option.
• -S, --set VAR=VALUE — Set variables for psql session.
• -R, --runtests — Run xUnit test using runtests().
• -s, --schema SCHEMA — Schema in which to find xUnit tests.
• -x, --match REGEX — Regular expression to find xUnit tests.
• --ext — Set the extension for tests (default .pg)
• -r, --recurse — Recursively descend into directories.
• --ignore-exit — Ignore exit status from test scripts.
• --trap — Trap Ctrl-C and print summary on interrupt.
• --harness — Define test harness to use.
• -j, --jobs N — Run N test jobs in parallel (try 9.)
• --rc RCFILE — Process options from rcfile
• --norc — Don't process default .proverc
• --state OPTION=VALUE — Set persistent state options.
• -v, --verbose — Print all test lines.
• -f, --failures — Show failed tests.
• -o, --comments — Show comments and diagnostics.
• --directives — Only show results with TODO or SKIP directives.
• -q, --quiet — Suppress some test output while running tests.
• -Q, --QUIET — Only print summary results.
• --parse — Show full list of TAP parse errors, if any.
• --normalize — Normalize TAP output in verbose output
• -D, --dry — Dry run. Show test that would have run.
• --merge — Merge test scripts' STDERR and STDOUT.
• -t, --timer — Print elapsed time after each test.
• -c, --color — Colored test output (default).
• --nocolor — Do not color test output.
• --shuffle — Run the tests in random order.
• --reverse — Run the tests in reverse order.
• -a, --archive FILENAME — Store the resulting TAP in an archive file.
• --formatter — Result formatter to use.
• --count — Show X/Y test count when not verbose (default)
• --nocount — Disable the X/Y test count.
• -H, --help — Print a usage statement and exit.
• -? — Print a usage statement and exit.
• -m, --man — Print the complete documentation and exit.
• -V, --version — Print the version number and exit.

---

💖 Options Details 💖

🗄️ Database Options 🗄️

-b, --psql-bin

pg_prove --psql-bin /usr/local/pgsql/bin/psql
pg_prove -b /usr/local/bin/psql

Path to the psql program, which will be used to actually run the tests. Defaults to psql, which should work well, when it is in your path. 💅

-d, --dbname

pg_prove --dbname try
pg_prove -d postgres

The name of database to which to connect. Defaults to the value of the $PGDATABASE environment variable or to the system username.

-U, --username

pg_prove --username foo
pg_prove -U postgres

PostgreSQL user name to connect as. Defaults to the value of the $PGUSER environment variable or to the operating system name of the user running the application.

-h, --host

pg_prove --host pg.example.com
pg_prove -h dev.local

Specifies the host name of the machine on which the server is running. If the value begins with a slash, it is used as the directory for the Unix-domain socket. Defaults to the value of the $PGHOST environment variable or localhost.

-p, --port

pg_prove --port 1234
pg_prove -p 666

Specifies the TCP port or the local Unix-domain socket file extension on which the server is listening for connections. Defaults to the value of the $PGPORT environment variable or, if not set, to the port specified at compile time, usually 5432.

-P, --pset

pg_prove --pset tuples_only=0
pg_prove -P null=[NULL]

Specifies printing options in the style of \pset in the psql program. See https://www.postgresql.org/docs/current/static/app-psql.html for details on the supported options.

-S, --set

pg_prove --set MY_CONTRACT=321
pg_prove -S TEST_SEARCH_PATH=test,public

Sets local variables for psql in the style of \set in the psql program. See https://www.postgresql.org/docs/current/static/app-psql.html for details on the supported options.

--runtests

pg_prove --runtests
pg_prove -r

Don't run any test scripts, but just use the runtests() pgTAP function to run xUnit tests. This ends up looking like a single test script has been run, when in fact no test scripts have been run. Instead, pg_prove tells psql to run something like:

psql --command 'SELECT * FROM runtests()'

You should use this option when you've written your tests in xUnit style, where they're all defined in test functions already loaded in the database.

-s, --schema

pg_prove --schema test
pg_prove -s mytest

Used with --runtests, and, in fact, implicitly forces --runtests to be true. This option can be used to specify the name of a schema in which to find xUnit functions to run. Basically, it tells psql to run something like:

psql --command "SELECT * FROM runtests('test'::name)"

-x, --match

pg_prove --match 'test$'
pg_prove -x _test_

Used with --runtests, and, in fact, implicitly forces --runtests to be true. This option can be used to specify a POSIX regular expression that will be used to search for xUnit functions to run. Basically, it tells psql to run something like:

psql --command "SELECT * FROM runtests('_test_'::text)"

This will run any visible functions with the string _test_ in their names. This can be especially useful if you just want to run a single test in a given schema. For example, this:

pg_prove --schema testing --match '^test_widgets$'

Will have psql execute the runtests() function like so:

SELECT * FROM runtests('testing'::name, '^test_widgets$'::text);

🎭 Behavioral Options 🎭

--ext

pg_prove --ext .sql tests/

Set the extension for test files (default .pg). May be specified multiple times if you have test scripts with multiple extensions, though all must be pgTAP tests:

pg_prove --ext .sql --ext .pg --ext .pgt

If you want to mix pgTAP tests with other TAP-emitting tests, like Perl tests, use prove instead, where --ext identifies any test file, and --pgtap-option suffix= lets you specify one or more extensions for pgTAP tests.

prove --source Perl \
      --ext .t --ext .pg \
      --source pgTAP --pgtap-option suffix=.pg

-r, --recurse

pg_prove --recurse tests/
pg_prove --recurse sql/

Recursively descend into directories when searching for tests. Be sure to specify --ext if your tests do not end in .pg. Not relevant with --runtests.

--ignore-exit

pg_prove --ignore-exit

• --ignore-exit — ignore the exit status coming back from test scripts. Normally if a script triggers a database exception, psql exits with an error code and, even if literally every test passed, the whole thing gets counted as a failure. Use --ignore-exit to ignore those situations — (at your own peril, bestie 💀)
• --trap — trap Ctrl-C and print a summary on interrupt.

pg_prove --trap

• --harness — specify a subclass of TAP::Harness to use for the test harness. Defaults to TAP::Harness — unless --archive is specified, in which case it uses TAP::Harness::Archive.

pg_prove --harness TAP::Harness::Color

• -j, -jobs — run N test jobs in parallel (try 9.) parallelism era ✨
• --rc — process options from the specified configuration file.

pg_prove --rc pg_prove.rc

If --rc isn't specified and ./.proverc or ~/.proverc exist, they get read and the options they contain get processed before the command line options. Options in config files are written exactly the same way as command line options:

# .proverc
--state=hot,fast,save
-j9

Under Windows and VMS the option file is named _proverc rather than .proverc, and it's only looked for in the current directory.

Because of how options are loaded you cannot use .proverc for pg_prove-specific options — only prove options. That said, pg_prove does support all of the usual libpq environment variables (the docs live at https://www.postgresql.org/docs/current/static/libpq-envars.html). 💖

• --norc — do not process ./.proverc or ~/.proverc.
• --state — this one lets you ask pg_prove to remember the state of previous test runs and then select and/or order the tests based on that saved state. 👑

The --state switch needs an argument, and that argument has to be a comma-separated list of one or more of the options below:

• last — run the same tests as the last time the state was saved. This makes it possible, for example, to recreate the ordering of a shuffled test.

# Run all tests in random order
pg_prove --state save --shuffle

# Run them again in the same order
pg_prove --state last

• failed — run only the tests that failed on the last run.

# Run all tests
pg_prove --state save

# Run failures
pg_prove --state failed

If you also specify the save option, newly passing tests will be excluded from subsequent runs.

# Repeat until no more failures
pg_prove --state failed,save

• passed — run only the tests that passed last time. Super useful for making sure no new problems have snuck in. 🦋
• all — run all tests in normal order. You can specify multiple options, so to run all tests with last time's failures first:

pg_prove --state failed,all,save

• hot — run the tests that most recently failed first. The last failure time of each test is stored, and hot makes tests run in most-recent-failure order.

pg_prove --state hot,save

Tests that have never failed won't be selected. To run all tests with the most recently failed first, use:

pg_prove --state hot,all,save

This same combination of options may also be specified like this:

pg_prove --state adrian

• todo — run any tests with todos.
• slow — run the tests in slowest-to-fastest order. This is chef's kiss alongside the -j parallel testing switch, because it makes sure your slowest tests start running first.

pg_prove --state slow -j9

• fast — run the tests in fastest-to-slowest order.
• new — run the tests in newest-to-oldest order, based on the modification times of the test scripts.
• old — run the tests in oldest-to-newest order.
• fresh — run those test scripts that have been modified since the last test run.
• save — save the state on exit. The state is stored in a file called .pg_prove (_pg_prove on Windows and VMS) in the current directory.

The --state switch may be used more than once. 💅

pg_prove --state hot --state all,save

---

✨ Display Options ✨

• -v, --verbose — display standard output of test scripts while they're running. You can also trigger this behavior by setting the $TEST_VERBOSE environment variable to a true value.

pg_prove --verbose
pg_prove -v

• -f, --failures — show failed tests.

pg_prove --failures
pg_prove -f

• -o, --comments — show comments, like diagnostics output by diag(). It's enabled by default, so use --no-comments to disable it.
• --directives — only show results with TODO or SKIP directives.

pg_prove --directives

• -q, --quiet — suppress some test output while running tests.

pg_prove --quiet
pg_prove -q

• -Q, --QUIET — only print summary results. (yes, uppercase bestie, they're different 👀)

pg_prove --QUIET
pg_prove -Q

• --parse — enables the display of any TAP parsing errors as tests run. Handy for debugging new TAP emitters.

pg_prove --parse

• --normalize — normalize TAP output in verbose output. Errors in the harnessed TAP that get corrected by the parser will be corrected.

pg_prove --normalize

• --dry, -D — dry run. Just outputs a list of the tests that would have been run.

pg_prove --dry tests/
pg_prove -D

• --merge — merge test scripts' STDERR with their STDOUT. Not really relevant to pgTAP tests, which only print to STDERR when an exception is thrown.
• -t, --timer — print elapsed time after each test file. ⏱️

pg_prove --timer
pg_prove -t

• -c, --color — display test results in color. Colored test output is the default, but if the output isn't going to a terminal, color gets disabled.

Requires Term::ANSIColor on Unix-like platforms and Win32::Console on Windows. If the necessary module isn't installed, colored output won't be available. 😭

pg_prove --color
pg_prove -c

• --nocolor — do not display test results in color.
• --shuffle — test scripts are normally run in alphabetical order. Use --reverse to run them in in random order. Not relevant when used with --runtests.

pg_prove --shuffle tests/

okay so the manpage really does say --reverse in that sentence and I'm just relaying it verbatim, don't come for me 🙈

• --reverse — test scripts are normally run in alphabetical order. Use --reverse to run them in reverse order. Not relevant when used with --runtests.

pg_prove --reverse tests/

• -a, --archive — send the TAP output to a TAP archive file as well as to the normal output destination. The archive formats supported are .tar and .tar.gz. 🎀

pg_prove --archive tap.tar.gz
pg_prove -a test_output.tar

• -f, --formatter — the name of the class to use to format output. The default is TAP::Formatter::Console, or TAP::Formatter::File if the output isn't a TTY.

pg_prove --formatter TAP::Formatter::File
pg_prove -f TAP::Formatter::Console

• --count — show the X/Y test count as tests run when not verbose (this is the default).

pg_prove --count

• --nocount — disable the display of the X/Y test count as tests run.

pg_prove --nocount

---

🌸 Metadata Options 🌸

• -H, -?, --help — outputs a brief description of the options supported by pg_prove and exits.

pg_prove --help
pg_prove -H

• -m, --man — outputs this documentation and exits. (hi, you're reading it 🫶)

pg_prove --man
pg_prove -m

• -V, --version — outputs the program name and version and exits.

pg_prove --version
pg_prove -V

---

👑 Author 👑

David E. Wheeler <dwheeler@cpan.org>

---

💜 Copyright 💜

Copyright (c) 2008-2025 David E. Wheeler. Some Rights Reserved.

---

and that's the whole page, bestie ✨ this one's from perl v5.40.1, dated 2025-04-09, and it ate, no cap. go write some tests 💅