{"id":37854,"date":"2025-08-29T18:23:47","date_gmt":"2025-08-29T16:23:47","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/?p=37854"},"modified":"2025-08-29T18:23:50","modified_gmt":"2025-08-29T16:23:50","slug":"unit-testing-postgresql-with-pgtap","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/unit-testing-postgresql-with-pgtap\/","title":{"rendered":"Unit Testing PostgreSQL with pgTAP"},"content":{"rendered":"
\n
\"\"<\/figure>\n<\/div>\n\n\n

Introduction<\/h2>\n\n\n\n

Unit testing is a fundamental practice in software development, ensuring that individual components function correctly. When working with PostgreSQL, testing database logic\u2014such as functions, triggers, and constraints\u2014is crucial for maintaining data integrity and reliability. One powerful tool for this purpose is pgTAP<\/strong>.
pgTAP is a PostgreSQL extension that provides a set of TAP (Test Anything Protocol) functions for writing unit tests directly in SQL. It allows developers to test database functions, schemas, constraints, and much more in an automated and repeatable way.<\/p>\n\n\n\n

Installing pgTAP<\/h2>\n\n\n\n

Before using pgTAP, you need to install it on your PostgreSQL instance. You can install it from source as follows:<\/p>\n\n\n

\n10:18:56 postgres@ws-pgdev:\/home\/postgres\/ [sw] wget https:\/\/api.pgxn.org\/dist\/pgtap\/1.3.3\/pgtap-1.3.3.zip .\n--2025-04-02 10:19:53--  https:\/\/api.pgxn.org\/dist\/pgtap\/1.3.3\/pgtap-1.3.3.zip\nResolving api.pgxn.org (api.pgxn.org)... 88.198.49.178\nConnecting to api.pgxn.org (api.pgxn.org)|88.198.49.178|:443... connected.\nHTTP request sent, awaiting response... 200 OK\nLength: 329966 (322K) [application\/zip]\nSaving to: \u2018pgtap-1.3.3.zip\u2019\n\npgtap-1.3.3.zip                         100%[============================================================================>] 322.23K  --.-KB\/s    in 0.1s\n\n2025-04-02 10:19:54 (3.18 MB\/s) - \u2018pgtap-1.3.3.zip\u2019 saved [329966\/329966]\n\n--2025-04-02 10:19:54--  http:\/\/.\/\nResolving . (.)... failed: No address associated with hostname.\nwget: unable to resolve host address \u2018.\u2019\nFINISHED --2025-04-02 10:19:54--\nTotal wall clock time: 0.4s\nDownloaded: 1 files, 322K in 0.1s (3.18 MB\/s)\n\n10:19:54 postgres@ws-pgdev:\/home\/postgres\/ [sw] unzip pgtap-1.3.3.zip\nArchive:  pgtap-1.3.3.zip\nb941782fada240afdb7057065eb3261a21e8512c\n   creating: pgtap-1.3.3\/\n  inflating: pgtap-1.3.3\/Changes\n...\n...\n\n10:20:11 postgres@ws-pgdev:\/home\/postgres\/ [sw] cd pgtap-1.3.3\/\n11:11:58 postgres@ws-pgdev:\/home\/postgres\/pgtap-1.3.3\/ [sw] make\n\nGNUmake running against Postgres version 17.0, with pg_config located at \/u01\/app\/postgres\/product\/17\/db_0\/bin\n\nMakefile:186: To use pg_prove, TAP::Parser::SourceHandler::pgTAP Perl module\nMakefile:187: must be installed from CPAN. To do so, simply run:\nMakefile:188: cpan TAP::Parser::SourceHandler::pgTAP\ncp sql\/pgtap--0.95.0--0.96.0.sql.in sql\/pgtap--0.95.0--0.96.0.sql\ncp sql\/pgtap--0.96.0--0.97.0.sql.in sql\/pgtap--0.96.0--0.97.0.sql\ncp sql\/pgtap--0.97.0--0.98.0.sql.in sql\/pgtap--0.97.0--0.98.0.sql\ncp sql\/pgtap--0.98.0--0.99.0.sql.in sql\/pgtap--0.98.0--0.99.0.sql\ncp sql\/pgtap--0.99.0--1.0.0.sql.in sql\/pgtap--0.99.0--1.0.0.sql\ncp sql\/pgtap.sql.in sql\/pgtap.sql\nsed -e 's,MODULE_PATHNAME,$libdir\/pgtap,g' -e 's,__OS__,linux,g' -e 's,__VERSION__,1.3,g' sql\/pgtap.sql > sql\/pgtap.tmp\nmv sql\/pgtap.tmp sql\/pgtap.sql\n'\/usr\/bin\/perl' -e 'for (grep { \/^CREATE \/} reverse <>) { chomp; s\/CREATE (OR REPLACE )?\/DROP \/; s\/DROP (FUNCTION|VIEW|TYPE) \/DROP $1 IF EXISTS \/; s\/ (DEFAUL                                T|=)[ ]+[a-zA-Z0-9]+\/\/g; print "$_;\\n" }' sql\/pgtap.sql > sql\/uninstall_pgtap.sql\ncp sql\/pgtap.sql.in sql\/pgtap-static.sql.tmp\n\n*** Patching pgtap-static.sql with compat\/install-9.6.patch\npatching file sql\/pgtap-static.sql.tmp\n\n*** Patching pgtap-static.sql with compat\/install-9.4.patch\npatching file sql\/pgtap-static.sql.tmp\n\n*** Patching pgtap-static.sql with compat\/install-9.2.patch\npatching file sql\/pgtap-static.sql.tmp\n\n*** Patching pgtap-static.sql with compat\/install-9.1.patch\npatching file sql\/pgtap-static.sql.tmp\nsed -e 's#MODULE_PATHNAME#$libdir\/pgtap#g' -e 's#__OS__#linux#g' -e 's#__VERSION__#1.3#g' sql\/pgtap-static.sql.tmp > sql\/pgtap-static.sql\n'\/usr\/bin\/perl' compat\/gencore 0 sql\/pgtap-static.sql > sql\/pgtap-core.sql\n'\/usr\/bin\/perl' compat\/gencore 1 sql\/pgtap-static.sql > sql\/pgtap-schema.sql\ncp sql\/pgtap.sql sql\/pgtap--1.3.3.sql\ncp sql\/pgtap-core.sql sql\/pgtap-core--1.3.3.sql\ncp sql\/pgtap-schema.sql sql\/pgtap-schema--1.3.3.sql\n\n11:12:02 postgres@ws-pgdev:\/home\/postgres\/pgtap-1.3.3\/ [sw] make install\n\nGNUmake running against Postgres version 17.0, with pg_config located at \/u01\/app\/postgres\/product\/17\/db_0\/bin\n\nMakefile:186: To use pg_prove, TAP::Parser::SourceHandler::pgTAP Perl module\nMakefile:187: must be installed from CPAN. To do so, simply run:\nMakefile:188: cpan TAP::Parser::SourceHandler::pgTAP\nmkdir -p '\/u01\/app\/postgres\/product\/17\/db_0\/share\/extension'\nmkdir -p '\/u01\/app\/postgres\/product\/17\/db_0\/share\/extension'\nmkdir -p '\/u01\/app\/postgres\/product\/17\/db_0\/share\/doc\/extension'\n\/bin\/sh \/u01\/app\/postgres\/product\/17\/db_0\/lib\/pgxs\/src\/makefiles\/..\/..\/config\/install-sh -c -m 644 .\/\/pgtap.control '\/u01\/app\/postgres\/product\/17\/db_0\/share\/extension\/'\n\/bin\/sh \/u01\/app\/postgres\/product\/17\/db_0\/lib\/pgxs\/src\/makefiles\/..\/..\/config\/install-sh -c -m 644 .\/\/sql\/pgtap--0.90.0--0.91.0.sql .\/\/sql\/pgtap--0.91.0--0.92.0.sql .\/\/sql\/pgtap--0.92.0--0.93.0.sql .\/\/sql\/pgtap--0.93.0--0.94.0.sql .\/\/sql\/pgtap--0.94.0--0.95.0.sql .\/\/sql\/pgtap--0.95.0--0.96.0.sql .\/\/sql\/pgtap--0.96.0--0.97.0.sql .\/\/sql\/pgtap--0.97.0--0.98.0.sql .\/\/sql\/pgtap--0.98.0--0.99.0.sql .\/\/sql\/pgtap--0.99.0--1.0.0.sql .\/\/sql\/pgtap--1.0.0--1.1.0.sql .\/\/sql\/pgtap--1.1.0--1.2.0.sql .\/\/sql\/pgtap--1.2.0--1.3.0.sql .\/\/sql\/pgtap--1.3.0--1.3.1.sql .\/\/sql\/pgtap--1.3.1--1.3.2.sql .\/\/sql\/pgtap--1.3.2--1.3.3.sql .\/\/sql\/pgtap--1.3.3.sql .\/\/sql\/pgtap--unpackaged--0.91.0.sql .\/\/sql\/pgtap-core--1.3.3.sql .\/\/sql\/pgtap-core.sql .\/\/sql\/pgtap-schema--1.3.3.sql .\/\/sql\/pgtap-schema.sql .\/\/sql\/pgtap.sql .\/\/sql\/uninstall_pgtap.sql  '\/u01\/app\/postgres\/product\/17\/db_0\/share\/extension\/'\n\/bin\/sh \/u01\/app\/postgres\/product\/17\/db_0\/lib\/pgxs\/src\/makefiles\/..\/..\/config\/install-sh -c -m 644 .\/\/doc\/pgtap.mmd '\/u01\/app\/postgres\/product\/17\/db_0\/share\/doc\/extension\/'\n<\/pre><\/div>\n\n\n
As mentioned in the output of the previous command, we need to run some commands to be able to use pg_prove, which we are going to use later:<\/p>\n\n\n
\n11:14:23 postgres@ws-pgdev:\/home\/postgres\/pgtap-1.3.3\/ [sw] cpan TAP::Parser::SourceHandler::pgTAP\nLoading internal logger. Log::Log4perl recommended for better logging\n\nCPAN.pm requires configuration, but most of it can be done automatically.\nIf you answer 'no' below, you will enter an interactive dialog for each\nconfiguration option instead.\n\nWould you like to configure as much as possible automatically? [yes] yes\n\nWarning: You do not have write permission for Perl library directories.\n\nTo install modules, you need to configure a local Perl library directory or\nescalate your privileges.  CPAN can help you by bootstrapping the local::lib\nmodule or by configuring itself to use 'sudo' (if available).  You may also\nresolve this problem manually if you need to customize your setup.\n\nWhat approach do you want?  (Choose 'local::lib', 'sudo' or 'manual')\n [local::lib] sudo\nFetching with HTTP::Tiny:\nhttps:\/\/cpan.org\/authors\/01mailrc.txt.gz\nReading '\/home\/postgres\/.cpan\/sources\/authors\/01mailrc.txt.gz'\n............................................................................DONE\nFetching with HTTP::Tiny:\nhttps:\/\/cpan.org\/modules\/02packages.details.txt.gz\nReading '\/home\/postgres\/.cpan\/sources\/modules\/02packages.details.txt.gz'\n  Database was generated on Wed, 02 Apr 2025 08:29:02 GMT\n..............\n  New CPAN.pm version (v2.38) available.\n  [Currently running version is v2.33]\n  You might want to try\n    install CPAN\n    reload cpan\n  to both upgrade CPAN.pm and run the new version without leaving\n  the current session.\n\n\n..............................................................DONE\nFetching with HTTP::Tiny:\nhttps:\/\/cpan.org\/modules\/03modlist.data.gz\nReading '\/home\/postgres\/.cpan\/sources\/modules\/03modlist.data.gz'\nDONE\nWriting \/home\/postgres\/.cpan\/Metadata\nRunning install for module 'TAP::Parser::SourceHandler::pgTAP'\nFetching with HTTP::Tiny:\nhttps:\/\/cpan.org\/authors\/id\/D\/DW\/DWHEELER\/TAP-Parser-SourceHandler-pgTAP-3.37.tar.gz\nFetching with HTTP::Tiny:\nhttps:\/\/cpan.org\/authors\/id\/D\/DW\/DWHEELER\/CHECKSUMS\nChecksum for \/home\/postgres\/.cpan\/sources\/authors\/id\/D\/DW\/DWHEELER\/TAP-Parser-SourceHandler-pgTAP-3.37.tar.gz ok\n'YAML' not installed, will not store persistent state\nConfiguring D\/DW\/DWHEELER\/TAP-Parser-SourceHandler-pgTAP-3.37.tar.gz with Build.PL\nCreated MYMETA.yml and MYMETA.json\nCreating new 'Build' script for 'TAP-Parser-SourceHandler-pgTAP' version '3.37'\n  DWHEELER\/TAP-Parser-SourceHandler-pgTAP-3.37.tar.gz\n  \/usr\/bin\/perl Build.PL --installdirs site -- OK\nRunning Build for D\/DW\/DWHEELER\/TAP-Parser-SourceHandler-pgTAP-3.37.tar.gz\nBuilding TAP-Parser-SourceHandler-pgTAP\n  DWHEELER\/TAP-Parser-SourceHandler-pgTAP-3.37.tar.gz\n  .\/Build -- OK\nRunning Build test for DWHEELER\/TAP-Parser-SourceHandler-pgTAP-3.37.tar.gz\nt\/source_handler.t .. ok\nAll tests successful.\nFiles=1, Tests=47,  1 wallclock secs ( 0.03 usr  0.00 sys +  0.08 cusr  0.12 csys =  0.23 CPU)\nResult: PASS\n  DWHEELER\/TAP-Parser-SourceHandler-pgTAP-3.37.tar.gz\n  .\/Build test -- OK\nRunning Build install for DWHEELER\/TAP-Parser-SourceHandler-pgTAP-3.37.tar.gz\nBuilding TAP-Parser-SourceHandler-pgTAP\nInstalling \/usr\/local\/man\/man1\/pg_prove.1p\nInstalling \/usr\/local\/man\/man1\/pg_tapgen.1p\nInstalling \/usr\/local\/share\/perl\/5.36.0\/TAP\/Parser\/SourceHandler\/pgTAP.pm\nInstalling \/usr\/local\/man\/man3\/TAP::Parser::SourceHandler::pgTAP.3pm\nInstalling \/usr\/local\/bin\/pg_tapgen\nInstalling \/usr\/local\/bin\/pg_prove\n  DWHEELER\/TAP-Parser-SourceHandler-pgTAP-3.37.tar.gz\n  sudo .\/Build install  -- OK\n11:15:03 postgres@ws-pgdev:\/home\/postgres\/pgtap-1.3.3\/ [sw] mkdir -p '\/u01\/app\/postgres\/product\/17\/db_0\/share\/extension'\n11:15:05 postgres@ws-pgdev:\/home\/postgres\/pgtap-1.3.3\/ [sw] mkdir -p '\/u01\/app\/postgres\/product\/17\/db_0\/share\/extension'\nmkdir -p '\/u01\/app\/postgres\/product\/17\/db_0\/share\/doc\/extension'\n11:15:10 postgres@ws-pgdev:\/home\/postgres\/pgtap-1.3.3\/ [sw] \/bin\/sh \/u01\/app\/postgres\/product\/17\/db_0\/lib\/pgxs\/src\/makefiles\/..\/..\/config\/install-sh -c -m 64                                4 .\/\/pgtap.control '\/u01\/app\/postgres\/product\/17\/db_0\/share\/extension\/'\n11:15:18 postgres@ws-pgdev:\/home\/postgres\/pgtap-1.3.3\/ [sw] \/bin\/sh \/u01\/app\/postgres\/product\/17\/db_0\/lib\/pgxs\/src\/makefiles\/..\/..\/config\/install-sh -c -m 64                                4 .\/\/sql\/pgtap--0.90.0--0.91.0.sql .\/\/sql\/pgtap--0.91.0--0.92.0.sql .\/\/sql\/pgtap--0.92.0--0.93.0.sql .\/\/sql\/pgtap--0.93.0--0.94.0.sql .\/\/sql\/pgtap--0.94.0--0                                .95.0.sql .\/\/sql\/pgtap--0.95.0--0.96.0.sql .\/\/sql\/pgtap--0.96.0--0.97.0.sql .\/\/sql\/pgtap--0.97.0--0.98.0.sql .\/\/sql\/pgtap--0.98.0--0.99.0.sql .\/\/sql\/pgtap--0                                .99.0--1.0.0.sql .\/\/sql\/pgtap--1.0.0--1.1.0.sql .\/\/sql\/pgtap--1.1.0--1.2.0.sql .\/\/sql\/pgtap--1.2.0--1.3.0.sql .\/\/sql\/pgtap--1.3.0--1.3.1.sql .\/\/sql\/pgtap--1.                                3.1--1.3.2.sql .\/\/sql\/pgtap--1.3.2--1.3.3.sql .\/\/sql\/pgtap--1.3.3.sql .\/\/sql\/pgtap--unpackaged--0.91.0.sql .\/\/sql\/pgtap-core--1.3.3.sql .\/\/sql\/pgtap-core.sql                                 .\/\/sql\/pgtap-schema--1.3.3.sql .\/\/sql\/pgtap-schema.sql .\/\/sql\/pgtap.sql .\/\/sql\/uninstall_pgtap.sql  '\/u01\/app\/postgres\/product\/17\/db_0\/share\/extension\/'\n11:15:33 postgres@ws-pgdev:\/home\/postgres\/pgtap-1.3.3\/ [sw] \/bin\/sh \/u01\/app\/postgres\/product\/17\/db_0\/lib\/pgxs\/src\/makefiles\/..\/..\/config\/install-sh -c -m 64                                4 .\/\/doc\/pgtap.mmd '\/u01\/app\/postgres\/product\/17\/db_0\/share\/doc\/extension\/'\n11:15:37 postgres@ws-pgdev:\/home\/postgres\/pgtap-1.3.3\/ [sw] pg_prove\nNo tests named and 't' directory not found at \/usr\/share\/perl\/5.36\/App\/Prove.pm line 522.\n\n11:15:42 postgres@ws-pgdev:\/home\/postgres\/pgtap-1.3.3\/ [sw] pg_prove --version\npg_prove 3.37\n<\/pre><\/div>\n\n\n
You can check if pgTAP was installed properly using the following command:<\/p>\n\n\n
\n10:24:09 postgres@ws-pgdev:\/home\/postgres\/pgtap-1.3.3\/ [sw] make installcheck\n\nGNUmake running against Postgres version 17.0, with pg_config located at \/u01\/app\/postgres\/product\/17\/db_0\/bin\n\nMakefile:186: To use pg_prove, TAP::Parser::SourceHandler::pgTAP Perl module\nMakefile:187: must be installed from CPAN. To do so, simply run:\nMakefile:188: cpan TAP::Parser::SourceHandler::pgTAP\nUsing 89 parallel test connections\nRebuilding test\/build\/all_tests\nSchedule changed to test\/build\/parallel.sch\ncp `cat test\/build\/which_schedule` test\/build\/run.sch\necho "# +++ regress install-check in  +++" && \/u01\/app\/postgres\/product\/17\/db_0\/lib\/pgxs\/src\/makefiles\/..\/..\/src\/test\/regress\/pg_regress --inputdir=.\/ --bindir='\/u01\/app\/postgres\/product\/17\/db_0\/bin'    --inputdir=test --max-connections=89 --schedule test\/schedule\/main.sch   --schedule test\/build\/run.sch\n# +++ regress install-check in  +++\n# using postmaster on Unix socket, port 5432\nok 1         - build                                     369 ms\n...\nok 4         - hastap                                   1309 ms\n# parallel group (35 tests):  matching istap do_tap moretap util performs_ok performs_within todotap check cmpok pg73 runjusttests roletap throwtap trigger usergroup enumtap policy runtests runnotests proctap fktap privs inheritance partitions valueset functap resultset aretap extension ownership ruletap pktap index unique\nok 5         + aretap                                   5911 ms\nok 6         + check                                    1558 ms\n...\nok 39        + valueset                                 3784 ms\n1..39\n# All 39 tests passed.\n<\/pre><\/div>\n\n\n
Once installed, enable it in your database:<\/p>\n\n\n
\n10:25:06 postgres@ws-pgdev:\/home\/postgres\/pgtap-1.3.3\/ [sw] psql\npsql (17.0 dbi services build)\nType "help" for help.\n\npostgres=# \\c hybrid\nYou are now connected to database "hybrid" as user "postgres".\nhybrid=# CREATE EXTENSION pgtap;\nCREATE EXTENSION\nhybrid=# \\dx\n                                                        List of installed extensions\n        Name        | Version |    Schema    |                                          Description\n--------------------+---------+--------------+-----------------------------------------------------------------------------------------------\n btree_gist         | 1.7     | training_app | support for indexing common datatypes in GiST\n orafce             | 4.14    | training_app | Functions and operators that emulate a subset of functions and packages from the Oracle RDBMS\n pg_stat_statements | 1.11    | public       | track planning and execution statistics of all SQL statements executed\n pg_trgm            | 1.6     | training_app | text similarity measurement and index searching based on trigrams\n pgcrypto           | 1.3     | training_app | cryptographic functions\n pgtap              | 1.3.3   | training_app | Unit testing for PostgreSQL\n plperl             | 1.0     | pg_catalog   | PL\/Perl procedural language\n plpgsql            | 1.0     | pg_catalog   | PL\/pgSQL procedural language\n(8 rows)\n<\/pre><\/div>\n\n\n
Writing Your First pgTAP Tests<\/h2>\n\n\n\n
pgTAP provides a wide range of assertions for testing various database objects. Let’s go through some examples.<\/p>\n\n\n\n
1. Testing a Function<\/h3>\n\n\n\n
Assume we have a function that verifies a password based on a specific pattern:<\/p>\n\n\n
\nCREATE OR REPLACE FUNCTION training_app.f_password_verify (pv_password TEXT) RETURNS BOOLEAN\nAS $$\n  SELECT pv_password ~ '^(?=.{10,}$)(?=.*[a-z])(?=.*[A-Z])(?=.*[0-9])(?=.*\\W).*$';\n$$ LANGUAGE sql;\n\nhybrid=# \\df f_password_verify\n                                List of functions\n    Schema    |       Name        | Result data type | Argument data types | Type\n--------------+-------------------+------------------+---------------------+------\n training_app | f_password_verify | boolean          | pv_password text    | func\n(1 row)\n\n<\/pre><\/div>\n\n\n
To test this function with pgTAP:<\/p>\n\n\n
\nhybrid=# SELECT plan(4);\n plan\n------\n 1..4\n(1 row)\n\nhybrid=# SELECT ok(training_app.f_password_verify('ValidPass1!'), 'Valid password should return true');\n                    ok\n------------------------------------------\n ok 1 - Valid password should return true\n(1 row)\n\nhybrid=# SELECT ok(NOT training_app.f_password_verify('short1!'), 'Too short password should return false');\n                      ok\n-----------------------------------------------\n ok 2 - Too short password should return false\n(1 row)\n\nhybrid=# SELECT ok(NOT training_app.f_password_verify('NoNumberPass!'), 'Password without a number should return false');\n                          ok\n------------------------------------------------------\n ok 3 - Password without a number should return false\n(1 row)\n\nhybrid=# SELECT ok(NOT training_app.f_password_verify('NoSpecialChar1'), 'Password without special character should return false');\n                              ok\n---------------------------------------------------------------\n ok 4 - Password without special character should return false\n(1 row)\n\nhybrid=# SELECT * FROM finish();\n finish\n--------\n(0 rows)\n\n<\/pre><\/div>\n\n\n
2. Testing Table Constraints<\/h3>\n\n\n\n
Consider the users<\/code> table with the following schema:<\/p>\n\n\n
\nhybrid=# \\d users\n                         Table "training_app.users"\n  Column  |   Type    | Collation | Nullable |           Default\n----------+-----------+-----------+----------+------------------------------\n id       | integer   |           | not null | generated always as identity\n username | text      |           | not null |\n password | text      |           | not null |\n created  | date      |           |          | now()\n validity | tstzrange |           |          |\nIndexes:\n    "users_pkey" PRIMARY KEY, btree (id)\n    "i_username_trgm" gin (username gin_trgm_ops)\n    "i_users_username" btree (username)\n    "i_users_username_btree_partial" btree (created) WHERE created >= '2024-11-07'::date AND created < '2024-11-08'::date\nCheck constraints:\n    "user_check_username" CHECK (username ~* '[A-Z0-9._%+-]+@[A-Z0-9.-]+\\.[A-Z]{2,4}'::text)\n    "user_check_username_length" CHECK (length(username) <= 72)\nReferenced by:\n    TABLE "user_training" CONSTRAINT "fk_user_training_users" FOREIGN KEY (user_id) REFERENCES users(id)\n    TABLE "users_history" CONSTRAINT "fk_users_history_user_id_users_id" FOREIGN KEY (user_id) REFERENCES users(id)\nPolicies:\n    POLICY "policy_current_month" FOR SELECT\n      TO role_app_read_only\n      USING (((EXTRACT(month FROM created))::integer = (EXTRACT(month FROM now()))::integer))\nTriggers:\n    t_log_user_history BEFORE UPDATE ON users FOR EACH ROW EXECUTE FUNCTION tf_user_history()\n\ncreate or replace function training_app.tf_user_history() returns trigger as\n$$\nbegin\n   insert into training_app.users_history ( user_id, username, password, created, validity)\n                                  values ( old.id, old.username, old.password, old.created, old.validity);\n   return new;\nend;                            \n$$ language plpgsql;\n\ncreate trigger t_log_user_history\n   before update on training_app.users\n   for each row\n   execute procedure training_app.tf_user_history();\n<\/pre><\/div>\n\n\n
To test the constraints, create a test file test_users_constraints.sql<\/code>:<\/p>\n\n\n
\n10:57:07 postgres@ws-pgdev:\/u01\/app\/postgres\/local\/dmk\/tests\/ [sw] touch test_users_constraints.sql\n10:57:37 postgres@ws-pgdev:\/u01\/app\/postgres\/local\/dmk\/tests\/ [sw] cat test_users_constraints.sql\n<\/pre><\/div>\n\n
\nBEGIN;\nSELECT plan(2);\n\n-- Test CHECK constraint on username format\nSELECT throws_like(\n  $$INSERT INTO training_app.users (username, password) VALUES ('invalid_user', 'Password1!')$$,\n  'new row for relation "users" violates check constraint "user_check_username"',\n  'Invalid username should fail CHECK constraint'\n);\n\n-- Test CHECK constraint on username length\nSELECT throws_like(\n  $$INSERT INTO training_app.users (username, password) VALUES (repeat('a', 73), 'Password1!')$$,\n  'new row for relation "users" violates check constraint "user_check_username"',\n  'Username exceeding 72 characters should fail CHECK constraint'\n);\n\nSELECT * FROM finish();\nROLLBACK;\n<\/pre><\/div>\n\n\n
Running Tests<\/h3>\n\n\n\n
You can execute pgTAP tests using pg_prove<\/code>, a command-line tool for running TAP tests. We are now going to test it with the file we just created, test_users_constraints.sql<\/em>.<\/p>\n\n\n\n
Run it with pg_prove<\/code>:<\/p>\n\n\n
\n11:45:14 postgres@ws-pgdev:\/u01\/app\/postgres\/local\/dmk\/tests\/ [sw] pg_prove -d hybrid -U postgres -p 5432 test_users_constraints.sql\ntest_users_constraints.sql .. ok\nAll tests successful.\nFiles=1, Tests=2,  0 wallclock secs ( 0.05 usr  0.01 sys +  0.00 cusr  0.01 csys =  0.07 CPU)\nResult: PASS\n<\/pre><\/div>\n\n\n
3. Testing Triggers<\/h3>\n\n\n\n
To verify that our trigger correctly logs changes to the users<\/code> table, we check:<\/p>\n\n\n\n