How to Use pgTAP in PostgreSQL for Reliable Database Testing

Introduction

As PostgreSQL databases grow in size and complexity, manual validation is no longer enough.

Modern databases contain:

• Hundreds of tables
• Thousands of constraints
• Critical indexes
• Business logic inside functions and triggers

For a Database Administrator, even a small unnoticed change can lead to:

• Performance regression
• Broken applications
• Silent data corruption

pgTAP solves this problem by bringing unit testing principles directly into PostgreSQL.

This article explains how to use pgTAP, why it matters for DBAs, and how to apply it with real, practical SQL examples.

What is pgTAP?

pgTAP is a PostgreSQL extension that allows you to write database tests in SQL, following the Test Anything Protocol (TAP).

In simple words, pgTAP lets you test your database schema, constraints, indexes, functions, and permissions in an automated, repeatable way.

It treats your database like code that can and should be tested.

Why pgTAP is Important for Database Administrators

pgTAP is especially powerful for DBAs because it helps to:

• Validate schema after migrations
• Prevent accidental loss of constraints or indexes
• Verify foreign keys and inheritance
• Ensure extensions and roles exist
• Detect regressions before production deployment

Unlike application tests, pgTAP tests the database itself.

Installing pgTAP

Step 1: Install the Package

sudo apt install postgresql-17-pgtap

Ensure the package version matches your PostgreSQL server version.

Step 2: Enable the Extension

CREATE EXTENSION pgtap;

Verify installation:

\dx+ pgtap

Once installed, pgTAP exposes hundreds of test helper functions.

pgTAP Testing Model (Core Concept)

Every pgTAP test session follows this lifecycle:

1. Declare how many tests you will run
2. Execute test assertions
3. Finish the test session

Example:

SELECT plan(1);
SELECT ok(true, 'pgTAP is working');
SELECT finish();

Important rules:

• plan() can be called only once
• Tests are automatically numbered
• Results are returned as TAP output

20+ Essential pgTAP Functions (With Purpose & Examples)

Below are the most useful pgTAP functions for DBAs, grouped by purpose.

1. ok (boolean, text)

Purpose:

Validate any logical condition. Used for sanity checks and basic assertions.

SELECT plan(5);
SELECT ok(1 = 1, 'Basic sanity check');

Result :

           ok             
---------------------------
 ok 1 - Basic sanity check
(1 row)

2. is(actual, expected, text)

Purpose:

Verify that a query result exactly matches an expected value.

SELECT is(
    (SELECT count(*) FROM res_partner),
    9604::bigint,
    'Expected number of partners'
);

Result :

                 is                  
-------------------------------------
 ok 23 - Expected number of partners
(1 row)

3. has_table(name)

Purpose:

Ensure a table exists. Critical after migrations.

SELECT has_table('res_partner');

Result :

               has_table               
---------------------------------------
 ok 2 - Table res_partner should exist
(1 row)

4. has_column(table, column)

Purpose:

Verify schema compatibility.

SELECT has_column('res_partner', 'id');

Result :

                has_column                 
-------------------------------------------
 ok 3 - Column res_partner.id should exist
(1 row)

5. col_type_is(table, column, type)

Purpose:

Detect accidental data type changes.

SELECT col_type_is('res_partner', 'id', 'integer');

Result :

                    col_type_is                     
-----------------------------------------------------
 ok 4 - Column res_partner.id should be type integer
(1 row)

6. col_not_null(table, column)

Purpose:

Ensure required fields cannot be NULL.

SELECT col_not_null('res_partner', 'id');

Result :

                  col_not_null                   
-------------------------------------------------
 ok 6 - Column res_partner.id should be NOT NULL
(1 row)

7. has_pk(table)

Purpose:

Confirm table identity and indexing.

SELECT has_pk('res_partner');

Result :

                       has_pk                       
----------------------------------------------------
 ok 7 - Table res_partner should have a primary key
(1 row)

8. col_is_pk(table, column

Purpose:

Ensure the correct column is the primary key.

SELECT col_is_pk('res_partner', 'id');

Result :

                       col_is_pk                       
-------------------------------------------------------
 ok 8 - Column res_partner(id) should be a primary key
(1 row)

9. has_index(table, index_name)

Purpose:

Prevent performance regressions caused by missing indexes.

SELECT has_index('res_partner', 'res_partner_pkey');

Result :

                 has_index                  
--------------------------------------------
 ok 9 - Index res_partner_pkey should exist
(1 row)

10. is_indexed(table, column)

Purpose:

Verify that a column is indexed (critical for query performance).

SELECT is_indexed('res_partner', 'id');

Result :

is_indexed 
------------
 ok 10
(1 row)

11. has_fk(table)

Purpose:

Ensure relational integrity exists.

SELECT has_fk('res_users');

Result :

                            has_fk                            
--------------------------------------------------------------
 ok 11 - Table res_users should have a foreign key constraint
(1 row)

12. col_is_fk(table, column)

Purpose:

Confirm that a column participates in a foreign key relationship.

SELECT col_is_fk('res_users', 'partner_id');

Result :

col_is_fk                           
--------------------------------------------------------------
 ok 12 - Column res_users(partner_id) should be a foreign key
(1 row)

13. has_inherited_tables(table)

Purpose:

Detect table inheritance usage.

SELECT has_inherited_tables('ir_actions');

Result :

              has_inherited_tables               
--------------------------------------------------
 ok 14 - Table ir_actions should have descendents
(1 row)

14. has_extension(name)

Purpose:

Verify required PostgreSQL extensions are installed.

SELECT has_extension('pg_stat_statements');

Result :

                   has_extension                   
---------------------------------------------------
 ok 15 - Extension pg_stat_statements should exist
(1 row)

15. has_schema(name)

Purpose:

Ensure expected schemas exist.

SELECT has_schema('public');

Result :

            has_schema             
------------------------------------
 ok 16 - Schema public should exist
(1 row)

16. has_role(name)

Purpose:

Verify database roles/users exist.

SELECT has_role('postgres');

Result :

              has_role              
------------------------------------
 ok 17 - Role postgres should exist
(1 row)

17. has_function(name, arg_types[])

Purpose:

Confirm critical functions exist.

SELECT has_function(
    'pg_sleep',
    ARRAY['double precision']
);

Result :

                       has_function                       
----------------------------------------------------------
 ok 18 - Function pg_sleep(double precision) should exist
(1 row)

18. function_returns(name, return_type)

Purpose:

Ensure function return types are correct.

SELECT function_returns('now', 'timestamp with time zone');

Result :

                       function_returns                        
---------------------------------------------------------------
 ok 19 - Function now() should return timestamp with time zone
(1 row)

19. alike(value, pattern, text)

Purpose:

Validate string patterns using LIKE.

SELECT alike(
    (SELECT name FROM res_partner LIMIT 1),
    '%e%',
    'Name contains letter e'
);

Result :

             alike              
--------------------------------
 ok 20 - Name contains letter e
(1 row)

20. is_empty(text, text)

Purpose:

Check whether a query result set is empty.

SELECT is_empty(
    'SELECT * FROM res_partner WHERE id = -1',
    'No invalid partner rows'
);

Result :

            is_empty             
---------------------------------
 ok 21 - No invalid partner rows
(1 row)

pgTAP Views (Important for DBAs)

pgTAP also provides helper views that expose metadata in a test-friendly way.

View 1: pg_all_foreign_keys

Purpose

This view provides a complete list of all foreign key relationships across the database.

It is extremely useful for:

• Auditing relationships
• Validating schema integrity
• Debugging broken references

Example Usage

SELECT *
FROM pg_all_foreign_keys limit 1;

Example :

-[ RECORD 1 ]------+--------------------------------------------------------
fk_schema_name     | public
fk_table_name      | account_account_account_tag
fk_constraint_name | account_account_account_tag_account_account_tag_id_fkey
fk_table_oid       | 3299744
fk_columns         | {account_account_tag_id}
pk_schema_name     | public
pk_table_name      | account_account_tag
pk_constraint_name | account_account_tag_pkey
pk_table_oid       | 3299755
pk_index_name      | account_account_tag_pkey
pk_columns         | {id}
match_type         | 
on_delete          | CASCADE
on_update          | NO ACTION
is_deferrable      | f
is_deferred        | f

View 2: tap_funky

Example Usage

SELECT * FROM tap_funky LIMIT 1;

Sample Result

oid          | 1242
schema       | pg_catalog
name         | boolin
owner        | postgres
args         | cstring
returns      | boolean
langoid      | 12
is_strict    | t
kind         | f
is_definer   | f
returns_set | f
volatility  | i
is_visible  | t

Purpose

tap_funky is an internal pgTAP helper view that exposes PostgreSQL function metadata in a structured and test-friendly format.

Rather than tracking test execution or results, this view acts as a catalog abstraction layer over PostgreSQL system tables such as pg_proc, pg_namespace, and pg_language.

What tap_funky Provides

For each function visible in the current database session, tap_funky reports:

• Function name and schema
• Function owner
• Argument signature
• Return type
• Programming language
• Volatility (immutable, stable, volatile)
• STRICT attribute
• SECURITY DEFINER status
• Set-returning behavior
• Visibility in the current search_path

Conclusion

pgTAP changes the way PostgreSQL databases are managed by replacing assumption-based validation with evidence-driven testing. Instead of trusting that schema changes, migrations, or upgrades worked correctly, pgTAP allows database administrators to prove that the database behaves exactly as intended.

For DBAs handling complex systems—such as ERP platforms, large production clusters, or frequently migrated databases—pgTAP is more than a utility. It establishes a testing mindset for database infrastructure, bringing the same discipline found in application development into the database layer.

By adopting pgTAP, administrators gain confidence in every change they deploy, reduce the risk of silent regressions, and improve long-term stability. When correctness, reliability, and maintainability matter, pgTAP is not optional—it becomes a core component of a professional PostgreSQL toolkit.