pg_prove - A command-line tool for running and harnessing pgTAP tests


  pg_prove -d template1 test*.sql
  pg_prove -d testdb -s testschema


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

Tests can be written and run in one of two ways, as SQL scripts or as 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.
    SELECT plan(1);

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

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

Now run the tests by passing the list of SQL script names to pg_prove. Here’s what it looks like when the pgTAP tests are run with pg_prove

    % pg_prove -U postgres sql/*.sql
    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:

        RETURN NEXT is( MAX(nick), NULL, 'Should have no users') FROM users;
        INSERT INTO users (nick) VALUES ('theory');
    $$ LANGUAGE plpgsql;

        SELECT is( nick, 'theory', 'Should have nick') FROM users;
    $$ 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 -d myapp -r
    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 “test” schema and ending with “test,” run the tests like so:

    pg_prove -d myapp --schema test --match 'test$'


  -b --psql-bin PSQL        Location of the psql program.
  -d --dbname DBNAME        Database to which to connect.
  -U --username USERNAME    Username 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 printing option.
  -v --verbose              Display output of test scripts while running them.
  -r --runtests             Run xUnit test using runtests().
  -s --schema               Schema in which to find xUnit tests.
  -x --match                Regular expression to find xUnit tests.
  -t --timer                Print elapsed time after each test file.
  -c --color                Display colored test ouput.
     --nocolor              Do not display colored test ouput.
  -f --formatter FORMATTER  TAP::Formatter class to format output.
  -a --archive FILENAME     Store the resulting TAP in an archive file.
  -H --help                 Print a usage statement and exit.
  -m --man                  Print the complete documentation and exit.
  -V --version              Print the version number and exit.

Options Details

  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.

  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.

  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.

  pg_prove --host
  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.

  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.

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

Specifies printing options in the style of \pset in the psql program. See for details on the supported options.

  pg_prove --verbose
  pg_prove -v

Display standard output of test scripts while running them. This behavior can also be triggered by setting the $TEST_VERBOSE environment variable to a true value.

  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.

  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)"
  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);
  pg_prove --timer
  pg_prove -t

Print elapsed time after each test file.

  pg_prove --color
  pg_prove -c

Display test results in color. Colored test output is the default, but if output is not to a terminal, color is disabled.

Requires Term::ANSIColor on Unix-like platforms and Win32::Console on Windows. If the necessary module is not installed colored output will not be available.


Do not display test results in color.

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

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 --archive tap.tar.gz
  pg_prove -a test_output.tar

Send the TAP output to a TAP archive file as wel as to the normal output destination. The archive formats supported are .tar and .tar.gz.

  pg_prove --help
  pg_prove -H

Outputs a brief description of the options supported by pg_prove and exits.

  pg_prove --man
  pg_prove -m

Outputs this documentation and exits.

  pg_prove --version
  pg_prove -V

Outputs the program name and version and exits.


David E. Wheeler <>


Copyright (c) 2008-2009 Kineticode, Inc. Some Rights Reserved.