pgtap
Unit testing for PostgreSQL
Overview
| Package | Version | Category | License | Language |
|---|---|---|---|---|
pgtap | 1.3.4 | LANG | PostgreSQL | C |
| ID | Extension | Bin | Lib | Load | Create | Trust | Reloc | Schema |
|---|---|---|---|---|---|---|---|---|
| 3200 | pgtap | No | Yes | No | Yes | No | No | - |
| Related | plpgsql_check plpgsql pldbgapi plprofiler faker unit dbt2 plperl |
|---|
missing pg17 el9, breaking perl deps
Version
| Type | Repo | Version | PG Ver | Package | Deps |
|---|---|---|---|---|---|
| EXT | PGDG | 1.3.4 | 1817161514 | pgtap | - |
| RPM | PGDG | 1.3.4 | 1817161514 | pgtap_$v | - |
| DEB | PGDG | 1.3.4 | 1817161514 | postgresql-$v-pgtap | - |
Install
You can install pgtap directly. First, make sure the PGDG repository is added and enabled:
pig repo add pgdg -u # Add PGDG repo and update cache
Install the extension using pig or apt/yum/dnf:
pig install pgtap; # Install for current active PG version
pig ext install -y pgtap -v 18 # PG 18
pig ext install -y pgtap -v 17 # PG 17
pig ext install -y pgtap -v 16 # PG 16
pig ext install -y pgtap -v 15 # PG 15
pig ext install -y pgtap -v 14 # PG 14
dnf install -y pgtap_18 # PG 18
dnf install -y pgtap_17 # PG 17
dnf install -y pgtap_16 # PG 16
dnf install -y pgtap_15 # PG 15
dnf install -y pgtap_14 # PG 14
apt install -y postgresql-18-pgtap # PG 18
apt install -y postgresql-17-pgtap # PG 17
apt install -y postgresql-16-pgtap # PG 16
apt install -y postgresql-15-pgtap # PG 15
apt install -y postgresql-14-pgtap # PG 14
Create Extension:
CREATE EXTENSION pgtap;
Usage
pgtap is a unit testing framework for PostgreSQL that produces TAP (Test Anything Protocol) output, providing hundreds of assertion functions for testing database objects and query results.
CREATE EXTENSION pgtap;
Test Structure
BEGIN;
SELECT plan(3); -- declare how many tests to run
SELECT ok(1 = 1, 'one equals one');
SELECT is(1 + 1, 2, 'addition works');
SELECT isnt(1, 2, 'one is not two');
SELECT * FROM finish();
ROLLBACK;
Use no_plan() when the test count is not known in advance:
BEGIN;
SELECT * FROM no_plan();
-- ... tests ...
SELECT * FROM finish();
ROLLBACK;
Basic Assertions
SELECT ok(expression, description); -- boolean test
SELECT is(got, expected, description); -- equality test
SELECT isnt(got, unexpected, description); -- inequality test
SELECT matches(value, regex, description); -- regex match
Schema Testing
SELECT has_table('users');
SELECT has_table('myschema', 'users', 'users table exists');
SELECT has_column('users', 'email');
SELECT col_type_is('users', 'email', 'text');
SELECT col_not_null('users', 'id');
SELECT col_has_default('users', 'created_at');
SELECT has_function('calculate_total');
SELECT has_function('calculate_total', ARRAY['integer', 'numeric']);
SELECT has_index('users', 'users_email_idx');
SELECT has_pk('users');
SELECT has_fk('orders');
Error Testing
SELECT lives_ok('INSERT INTO t(id) VALUES (1)', 'insert succeeds');
SELECT throws_ok(
'SELECT 1/0',
'22012', -- SQLSTATE for division by zero
'division by zero'
);
Query Result Testing
-- Compare ordered result sets
SELECT results_eq(
'SELECT * FROM active_users()',
'SELECT * FROM users WHERE active',
'active_users returns correct rows'
);
-- Compare unordered result sets
SELECT set_eq(
'SELECT * FROM active_ids()',
ARRAY[2, 3, 4, 5]
);
-- Check query returns no rows
SELECT is_empty('SELECT * FROM users WHERE id = -1');
-- Compare bag (multiset) results
SELECT bag_eq(
'SELECT color FROM items',
$$VALUES ('red'), ('blue'), ('red')$$
);
Running Tests with pg_prove
pg_prove -d mydb tests/*.sql
pg_prove -d mydb --ext .sql --recurse tests/
xUnit Style
CREATE FUNCTION test_my_feature() RETURNS SETOF text AS $$
RETURN NEXT ok(1 = 1, 'basic check');
RETURN NEXT is(my_func(1), 42, 'function works');
$$ LANGUAGE plpgsql;
SELECT * FROM runtests('test_my_feature');
Feedback
Was this page helpful?
Thanks for the feedback! Please let us know how we can improve.
Sorry to hear that. Please let us know how we can improve.