NOT VALID Still Rejects Writes: Stage a PostgreSQL CHECK Safely

A successful ALTER TABLE ... ADD CONSTRAINT ... CHECK ... NOT VALID answers only part of an integrity-migration question. It can mean the rule is installed and enforced for subsequent row versions while the table still contains older rows that violate that same rule. PostgreSQL deliberately separates those states: with NOT VALID, it skips the initial validation scan, still applies an enforced CHECK to subsequent inserts and updates, and does not consider the constraint validated for the existing population until VALIDATE CONSTRAINT succeeds. PostgreSQL 18's ALTER TABLE documentation states those behaviors explicitly.

That creates two acceptance claims, and they must be proved independently:

Admission claim: prohibited new row versions are rejected.

Population claim: every retained pre-existing row has been checked against the installed rule.

This playbook treats PostgreSQL 18 as a fixed documentation baseline rather than a claim about whichever PostgreSQL version happens to be newest when the article is read. The laboratory uses one disposable database, one ordinary nonpartitioned table, integer primary keys, separate application and migration roles, and separate psql sessions. The contract is deliberately narrow:

CHECK (amount >= 0)

A known amount must not be negative. NULL is allowed in this first contract. PostgreSQL CHECK semantics accept TRUE or UNKNOWN, so NULL >= 0 produces UNKNOWN and satisfies this CHECK; that is a business-rule choice here, not a validation failure. PostgreSQL 18's constraint documentation documents that distinction.

No terminal transcript below is represented as a measured result. The scripts define expected outcomes and, when executed, capture the actual server/client builds, SQLSTATEs, rows, locks, timeouts, and catalog state that should drive the decision.

Define the two promises and pin a controlled fixture

The central invariant is stronger than “the DDL succeeded.”

For full acceptance, every row version admitted after installation must satisfy the installed CHECK, and every retained legacy row must have passed the same rule before the constraint is described as validated. PostgreSQL's staged mechanism is useful precisely because those statements can temporarily differ: ADD ... NOT VALID skips the existing-row scan but still rejects later inserts or updates whose resulting row fails an enforced CHECK. A separate validation operation scans for legacy violations.

That distinction also gives proceed-staged a precise meaning. It is an interim operating state in which enforcement is active but legacy validation remains outstanding. It needs an owner, an identified debt population or discovery task, an approved remediation path and a deadline. It is not another spelling of “migration complete.” Broader database migration automation can transport these commands, but automation does not collapse the two database states into one. The referenced article discusses automated schema delivery and database operations; the acceptance evidence here remains specific to this CHECK.

Start from a disposable database. The following bootstrap uses two NOLOGIN roles so two local psql sessions can assume distinct application and migration identities without publishing sample passwords. Run the cluster-level bootstrap with a scratch administrative account that can create roles and databases. Do not reuse these names on a shared environment where they might already exist.

-- 00-bootstrap-cluster.sql
\set ON_ERROR_STOP on

CREATE ROLE check_lab_migrator NOLOGIN;
CREATE ROLE check_lab_app      NOLOGIN;

GRANT check_lab_migrator TO CURRENT_USER;
GRANT check_lab_app      TO CURRENT_USER;

CREATE DATABASE check_constraint_lab
  OWNER check_lab_migrator;

Then connect only to the disposable database for the actual migration laboratory:

-- 01-setup.sql
\set ON_ERROR_STOP on
\connect check_constraint_lab

SET ROLE check_lab_migrator;

CREATE SCHEMA lab AUTHORIZATION check_lab_migrator;

CREATE TABLE lab.payments (
    id     integer PRIMARY KEY,
    amount integer,
    note   text
);

INSERT INTO lab.payments (id, amount, note)
VALUES
    (1,  20, NULL),
    (2,  -5, NULL),
    (3, NULL, NULL);

GRANT USAGE ON SCHEMA lab TO check_lab_app;
GRANT SELECT, INSERT, UPDATE, DELETE
    ON lab.payments TO check_lab_app;

RESET ROLE;

The expected legacy population must be defined independently rather than reconstructed from whatever is currently in the table. Keep this as a separate evidence file:

-- expected_rows.sql
WITH expected(id, amount, note) AS (
    VALUES
        (1,  20, NULL::text),
        (2,  -5, NULL::text),
        (3, NULL::integer, NULL::text)
),
actual AS (
    SELECT id, amount, note
    FROM lab.payments
)
SELECT 'missing_or_changed' AS difference,
FROM (
    SELECT FROM expected
    EXCEPT
    SELECT FROM actual
) d
UNION ALL
SELECT 'unexpected_actual' AS difference,
FROM (
    SELECT FROM actual
    EXCEPT
    SELECT FROM expected
) d
ORDER BY difference, id;

Before DDL, capture an environment manifest. Do not pre-fill version values from documentation. SELECT version() and the shell-side psql --version are evidence of the actually installed server and client; a page labelled PostgreSQL 18 is a documentation selector, not an environment lock.

-- 02-manifest.sql
\set ON_ERROR_STOP on
\set schema_revision 'check_amount_nonnegative_r1'

SELECT clock_timestamp() AS captured_at;
SELECT version() AS exact_server_build;
SELECT current_setting('server_version') AS server_version,
       current_setting('server_version_num') AS server_version_num;

\! psql --version

SELECT current_database(),
       session_user,
       current_user,
       :'schema_revision' AS schema_revision;

SHOW default_transaction_isolation;
SHOW transaction_isolation;
SHOW transaction_read_only;
SHOW transaction_deferrable;
SHOW lock_timeout;
SHOW statement_timeout;

SELECT c.oid AS relation_oid,
       n.nspname AS schema_name,
       c.relname,
       c.relkind,
       c.relispartition
FROM pg_class AS c
JOIN pg_namespace AS n ON n.oid = c.relnamespace
WHERE c.oid = 'lab.payments'::regclass;

SELECT NOT EXISTS (
    SELECT 1
    FROM pg_inherits
    WHERE inhrelid  = 'lab.payments'::regclass
       OR inhparent = 'lab.payments'::regclass
) AS no_inheritance_edges;

The acceptance record should preserve the exact output, not a paraphrase such as “Postgres 18.” Record the schema revision, database name, relation OID, effective role, transaction settings and both timeouts beside the build strings. Those details make a later result attributable to a specific test context rather than merely plausible.

Read catalog state separately from installation state

Before adding the CHECK, define the catalog query that will be used at every transition. PostgreSQL 18's pg_constraint catalog documentation identifies contype = 'c' as a CHECK, conenforced as whether the constraint is enforced, convalidated as whether it has been validated, and conrelid as the relation to which a table constraint belongs. It also warns that constraint names are not necessarily unique, so querying by name alone is insufficient evidence.

-- state.sql
SELECT c.conname,
       pg_get_constraintdef(c.oid, true) AS definition,
       c.contype,
       c.conenforced,
       c.convalidated,
       c.conrelid::regclass AS relation
FROM pg_constraint AS c
WHERE c.conrelid = 'lab.payments'::regclass
  AND c.conname = 'payments_amount_nonnegative';

At the initial baseline this query should return no matching row. That absence is the independently defined starting state, not evidence inferred after the migration.

The expected state ledger is:

This ledger is valuable because a failed VALIDATE CONSTRAINT is not a mysterious halfway catalog mutation. PostgreSQL documents validation as a scan that marks a previously NOT VALID constraint valid when no violating rows are found. If the scan finds a violation, validation fails; it does not repair the row for you.

The terminology introduced in PostgreSQL 18 also makes one distinction essential. NOT VALID and NOT ENFORCED are not interchangeable. Under the canonical workflow here, do not write NOT ENFORCED.

The PostgreSQL 18 CREATE TABLE documentation says an ENFORCED constraint is checked by the database, while a NOT ENFORCED constraint is not checked and leaves responsibility to application code. Enforcement is the default. PostgreSQL 18 supports NOT ENFORCED for CHECK and foreign-key constraints. Conversely, NOT VALID on the enforced CHECK used here means the initial old-row verification is deferred while subsequent inserts and updates are still checked.

Install only the canonical rule:

-- 10-install.sql
\set ON_ERROR_STOP on

SET ROLE check_lab_migrator;

BEGIN;

SET LOCAL lock_timeout = '2s';
SET LOCAL statement_timeout = '30s';

ALTER TABLE lab.payments
    ADD CONSTRAINT payments_amount_nonnegative
    CHECK (amount >= 0)
    NOT VALID;

COMMIT;

RESET ROLE;

Those timeout values are laboratory choices, not production recommendations. PostgreSQL defines lock_timeout as limiting time spent waiting to acquire locks and statement_timeout as limiting overall statement execution time. A nonzero statement timeout that is shorter than or equal to the lock timeout can mask the distinction, which is why the playbook records both independently. PostgreSQL 18's client-setting documentation describes both controls.

After commit, run state.sql. The acceptance expectation is the exact CHECK definition, contype='c', conenforced=true, and convalidated=false. Then prove the legacy exception still physically exists:

SELECT id, amount, note
FROM lab.payments
WHERE id = 2;

The expected row remains (2, -5, NULL). That is not evidence that the CHECK is ineffective. It is the expected consequence of adding the enforced CHECK as NOT VALID: the old-row scan was deferred. PostgreSQL documents that NOT VALID skips that potentially lengthy scan while applying the constraint to later inserts and updates.

At this point assign an operational record such as:

schema revision: check_amount_nonnegative_r1
constraint: lab.payments.payments_amount_nonnegative
state: installed / enforced / not validated
business data owner: record the accountable person or team
technical owner: record the accountable person or team
legacy debt: row 2 is known negative in fixture
repair authorization: pending
repair deadline: record the approved date
release decision: proceed-staged / hold

A successful DDL command closes only the installation step.

Test admitted row versions and make NULL semantics explicit

The most important application-compatibility test is not an ad hoc SELECT amount < 0; it is to exercise actual writes through the ordinary application role.

This is where application and database compatibility becomes concrete. An application that previously assumed “any UPDATE of an old row is harmless if I do not touch amount” has acquired a new failure mode once this CHECK is installed. The database contract, not merely the DDL transport mechanism, determines compatibility.

Open an application session:

psql -X -d check_constraint_lab

Then establish the identity and session settings:

SET ROLE check_lab_app;
SET application_name = 'check-lab/application-probes';

SELECT current_database(), session_user, current_user;
SHOW lock_timeout;
SHOW statement_timeout;

SET lock_timeout = '2s';
SET statement_timeout = '15s';

Use explicit transactions and savepoints. For expected failures, temporarily allow psql to continue so the SQLSTATE can be captured and the failed subtransaction can be rolled back immediately.

First, prove a valid new row version is admitted:

BEGIN;

INSERT INTO lab.payments (id, amount, note)
VALUES (10, 7, 'positive write probe')
RETURNING id, amount, note;

ROLLBACK;

SELECT *
FROM lab.payments
WHERE id = 10;

The INSERT should succeed; the final query should find no persisted id=10 because the test transaction was deliberately rolled back.

Now test the prohibited insert:

\set ON_ERROR_STOP off

BEGIN;
SAVEPOINT probe_invalid_insert;

INSERT INTO lab.payments (id, amount, note)
VALUES (11, -1, 'must fail');

\echo invalid_insert_sqlstate=:SQLSTATE

ROLLBACK TO SAVEPOINT probe_invalid_insert;
COMMIT;

\set ON_ERROR_STOP on

SELECT *
FROM lab.payments
WHERE id = 11;

The expected PostgreSQL condition is SQLSTATE 23514, check_violation; PostgreSQL's error-code appendix assigns that SQLSTATE to CHECK violations. Capture the actual code from the test rather than accepting the expected value as evidence. The final SELECT should find no row.

Test an existing valid row becoming invalid:

\set ON_ERROR_STOP off

BEGIN;
SAVEPOINT probe_bad_update;

UPDATE lab.payments
SET amount = -20
WHERE id = 1;

\echo bad_update_sqlstate=:SQLSTATE

ROLLBACK TO SAVEPOINT probe_bad_update;
COMMIT;

\set ON_ERROR_STOP on

SELECT id, amount, note
FROM lab.payments
WHERE id = 1;

The update should be rejected, and id=1 should remain at amount=20.

The less obvious control is a note-only update to the legacy invalid row:

\set ON_ERROR_STOP off

BEGIN;
SAVEPOINT probe_note_only;

UPDATE lab.payments
SET note = 'note-only update'
WHERE id = 2;

\echo note_only_sqlstate=:SQLSTATE

ROLLBACK TO SAVEPOINT probe_note_only;
COMMIT;

\set ON_ERROR_STOP on

SELECT id, amount, note
FROM lab.payments
WHERE id = 2;

The expected outcome is another CHECK violation. PostgreSQL describes CHECK expressions as conditions that new or updated rows must satisfy, and the NOT VALID documentation says the constraint is applied to subsequent inserts or updates. The old stored tuple can remain untouched, but an UPDATE attempts to create a new row version and that resulting row still contains amount=-5.

That distinction prevents a dangerous operational misconception: legacy-invalid rows are not permanently exempt from enforcement. They are merely not retroactively scanned at installation time.

A repair is different from a note-only change. If the business owner has approved changing -5 to 5, the resulting row satisfies the CHECK and can be admitted:

BEGIN;

UPDATE lab.payments
SET amount = 5
WHERE id = 2
  AND amount = -5
RETURNING id, amount, note;

ROLLBACK;

Here the rollback keeps the write-probe phase nondestructive; the authorized permanent repair happens later.

Now make NULL semantics observable:

SELECT amount,
       amount >= 0 AS canonical_predicate
FROM (VALUES
    (20::integer),
    (-5::integer),
    (NULL::integer)
) AS v(amount);

Expected truth values are TRUE, FALSE and NULL/UNKNOWN respectively. PostgreSQL documents that a CHECK is satisfied when its expression is TRUE or NULL/UNKNOWN. Therefore CHECK (amount >= 0) deliberately allows NULL.

Prove admission with a real write:

BEGIN;

INSERT INTO lab.payments (id, amount, note)
VALUES (12, NULL, 'NULL control')
RETURNING *;

ROLLBACK;

Acceptance of that row is correct for the canonical contract.

A stricter business rule is semantically different:

SELECT amount,
       amount >= 0 AS nullable_contract,
       amount IS NOT NULL AND amount >= 0 AS strict_contract
FROM (VALUES
    (20::integer),
    (-5::integer),
    (NULL::integer)
) AS v(amount);

Run that comparison only as a semantic control in the disposable/reset fixture. The stricter predicate creates a newly failing population: legacy row id=3 would become invalid. Do not silently substitute it for the commissioned constraint. A requirement that amounts must always be known needs separate approval, impact analysis and remediation evidence. PostgreSQL also documents an explicit NOT NULL constraint as the normal mechanism for prohibiting null column values; this article is intentionally not turning that into a second migration guide.

Finally restore session settings:

RESET lock_timeout;
RESET statement_timeout;
RESET application_name;
RESET ROLE;

Preserve failed validation, then perform only an approved remediation

With the legacy -5 still stored, run validation before repairing it. The failure is useful evidence: it proves the test would detect the known dirty population rather than merely exercising a happy path.

In the migration session:

SET ROLE check_lab_migrator;
SET application_name = 'check-lab/validation-failure';

\set ON_ERROR_STOP off

BEGIN;

SET LOCAL lock_timeout = '2s';
SET LOCAL statement_timeout = '30s';

ALTER TABLE lab.payments
    VALIDATE CONSTRAINT payments_amount_nonnegative;

\echo validation_sqlstate=:SQLSTATE

ROLLBACK;

\set ON_ERROR_STOP on

For this fixture, validation is expected to fail because row 2 evaluates the predicate as FALSE. PostgreSQL documents VALIDATE CONSTRAINT as scanning the table to ensure no rows fail the previously NOT VALID constraint. A CHECK failure is expected to classify as SQLSTATE 23514; preserve the actual captured SQLSTATE with the evidence package.

The explicit ROLLBACK matters. After an error inside a transaction, PostgreSQL reports subsequent work in that transaction as in_failed_sql_transaction until the transaction is recovered; PostgreSQL assigns SQLSTATE 25P02 to that condition. A probe that ignores transaction recovery can make later commands fail for the wrong reason.

Now re-run the catalog query and inspect the old row:

SELECT c.conname,
       pg_get_constraintdef(c.oid, true) AS definition,
       c.contype,
       c.conenforced,
       c.convalidated
FROM pg_constraint AS c
WHERE c.conrelid = 'lab.payments'::regclass
  AND c.conname = 'payments_amount_nonnegative';

SELECT id, amount, note
FROM lab.payments
WHERE id = 2;

Expected state: the CHECK remains installed and enforced, convalidated=false, and row 2 remains -5. The failed validation should not be treated as permission to drop the rule automatically. Keeping it installed continues to protect newly admitted row versions while the legacy problem is resolved. That inference follows directly from PostgreSQL's documented separation of enforcement and validation.

Now introduce the only authorized data change in this laboratory. The repair ledger is independently defined:

This is not a claim that real accounting data should change sign. In a real migration, a negative amount could be correct business history, a coding defect, a reversal, a value requiring quarantine, or evidence that the proposed predicate itself is wrong. Correction, quarantine and deletion are separate business decisions. DDL does not authorize any of them.

The fixture repair is therefore guarded by both identity and expected old value:

-- 50-approved-remediation.sql
\set ON_ERROR_STOP on

SET ROLE check_lab_migrator;

BEGIN;

UPDATE lab.payments
SET amount = 5
WHERE id = 2
  AND amount = -5
RETURNING id, amount, note;

SELECT (:ROW_COUNT::integer = 1) AS repair_row_count_ok \gset

\if :repair_row_count_ok
    COMMIT;
\else
    ROLLBACK;
    \echo 'ABORT: remediation did not match exactly one expected row'
    \quit 3
\endif

The old-value predicate is not decorative. If somebody has already changed row 2 from -5 to another value, the approved mapping no longer describes the data being modified. The correct action is to stop and reassess, not broaden the WHERE clause until something updates.

Capture the permanent result:

SELECT id, amount, note
FROM lab.payments
ORDER BY id;

The expected fixture is now (1,20), (2,5), (3,NULL). Catalog state remains unvalidated until VALIDATE CONSTRAINT actually succeeds.

Bound lock acquisition and distinguish blocking from bad data

A staged CHECK rollout avoids the initial validation scan during ADD ... NOT VALID; it does not make DDL lock-free.

PostgreSQL's ALTER TABLE documentation says an ACCESS EXCLUSIVE lock is acquired unless a subform explicitly documents a weaker mode. ADD CHECK ... NOT VALID has no weaker CHECK-specific exception there, while VALIDATE CONSTRAINT explicitly acquires SHARE UPDATE EXCLUSIVE. PostgreSQL's lock documentation further shows that ACCESS EXCLUSIVE conflicts with every table-level lock mode and that SHARE UPDATE EXCLUSIVE conflicts with, among others, SHARE.

That is why database operating foundations matter here: the operational question is not “does the tiny fixture validate quickly?” but “can this session obtain the documented lock, and can we classify failure when it cannot?” The linked article supplies broader DBA context; PostgreSQL documentation is the authority for these lock semantics.

Use an explicit two-session barrier rather than sleep and hope.

Session A: blocker

-- 60-session-a-blocker.sql
\set ON_ERROR_STOP on

SET ROLE check_lab_migrator;
SET application_name = 'check-lab/blocker';

BEGIN;

LOCK TABLE lab.payments IN SHARE MODE;

SELECT 'BLOCKER_READY' AS barrier,
       pg_backend_pid() AS blocker_pid;

-- Stop here. Keep this transaction open until Session B records its timeout.

The SHARE lock intentionally conflicts with the SHARE UPDATE EXCLUSIVE lock required by validation. PostgreSQL's lock matrix documents that conflict.

Session B: validation attempt

-- 61-session-b-timeout.sql
SET ROLE check_lab_migrator;
SET application_name = 'check-lab/validator-timeout';

\set ON_ERROR_STOP off

BEGIN;

SET LOCAL lock_timeout = '1500ms';
SET LOCAL statement_timeout = '15s';

SHOW lock_timeout;
SHOW statement_timeout;

ALTER TABLE lab.payments
    VALIDATE CONSTRAINT payments_amount_nonnegative;

\echo blocking_probe_sqlstate=:SQLSTATE

ROLLBACK;

\set ON_ERROR_STOP on

With Session A still holding the conflicting lock, the expected failure category is a lock acquisition timeout, not a CHECK violation. PostgreSQL defines lock_timeout specifically around waiting for lock acquisition; the error-code catalog defines 55P03 as lock_not_available. Capture the actual SQLSTATE rather than inferring it from elapsed time or localized text.

You can inspect the active relationship from a third administrative observation session or, if appropriate permissions are available, from Session B before the timeout using pg_locks. PostgreSQL documents pg_locks as the system view for outstanding locks.

Once Session B has captured the timeout, release Session A deliberately:

-- Session A
COMMIT;

RESET application_name;
RESET ROLE;

This single controlled experiment separates three failure classes that should never be merged:

PostgreSQL defines statement_timeout across statement execution and lock_timeout only while waiting on locks, so preserving the setting values and SQLSTATE is necessary to interpret the event correctly.

Do not promote 1500ms, 15s or the fixture's scan time into production sizing recommendations. The laboratory contains three rows. It tests semantics and evidence collection, not production latency, table-scan throughput, queueing behavior or a zero-downtime claim.

The same caution applies to installation. Because the ordinary ADD CHECK form falls under the documented default ACCESS EXCLUSIVE behavior, lock acquisition for installation can conflict even when the expensive old-row verification has been deferred. NOT VALID reduces one class of work; it does not erase lock acquisition.

Validate, reconcile, and define a non-destructive rollback boundary

After the approved repair and the controlled lock probe, retry validation without the intentional blocker:

-- 70-validate-pass.sql
\set ON_ERROR_STOP on

SET ROLE check_lab_migrator;
SET application_name = 'check-lab/final-validation';

BEGIN;

SET LOCAL lock_timeout = '2s';
SET LOCAL statement_timeout = '30s';

ALTER TABLE lab.payments
    VALIDATE CONSTRAINT payments_amount_nonnegative;

COMMIT;

Then query the exact relation and constraint:

SELECT c.conname,
       pg_get_constraintdef(c.oid, true) AS definition,
       c.contype,
       c.conenforced,
       c.convalidated,
       c.conrelid::regclass AS relation
FROM pg_constraint AS c
WHERE c.conrelid = 'lab.payments'::regclass
  AND c.conname = 'payments_amount_nonnegative';

Full catalog acceptance requires the expected predicate, contype='c', conenforced=true and convalidated=true. PostgreSQL defines those fields independently, so convalidated=true without verifying which relation and predicate you inspected would still be weak evidence.

Reconcile the retained data against an updated, independently approved post-remediation ledger:

WITH expected(id, amount, note) AS (
    VALUES
        (1, 20, NULL::text),
        (2,  5, NULL::text),
        (3, NULL::integer, NULL::text)
),
actual AS (
    SELECT id, amount, note
    FROM lab.payments
)
SELECT 'missing_or_changed' AS difference,
FROM (
    SELECT FROM expected
    EXCEPT
    SELECT FROM actual
) d
UNION ALL
SELECT 'unexpected_actual',
FROM (
    SELECT FROM actual
    EXCEPT
    SELECT FROM expected
) d
ORDER BY 1, 2;

An empty difference set proves only reconciliation against this fixture ledger. It does not by itself prove write enforcement, and it does not invent a non-null requirement.

Repeat the application positive and negative probes after validation. At minimum:

SET ROLE check_lab_app;

BEGIN;
INSERT INTO lab.payments(id, amount, note)
VALUES (20, 8, 'post-validation positive control');
ROLLBACK;

and:

\set ON_ERROR_STOP off

BEGIN;
SAVEPOINT p;

INSERT INTO lab.payments(id, amount, note)
VALUES (21, -8, 'post-validation negative control');

\echo post_validation_negative_sqlstate=:SQLSTATE

ROLLBACK TO SAVEPOINT p;
COMMIT;

\set ON_ERROR_STOP on

Also repeat the NULL control. Validation of CHECK (amount >= 0) does not mean “all amounts are known”; PostgreSQL's CHECK truth semantics continue to treat NULL/UNKNOWN as satisfying this predicate. This is an important case for correctness before query optimization: an empty WHERE amount < 0 result can be fast and still answer the wrong business question if someone has silently redefined the contract as “nonnegative and non-null.” The linked article emphasizes validating technical suggestions; here the correctness criterion is defined by the accepted predicate itself.

Rollback must also be divided into schema and data decisions.

To demonstrate schema reversion in this disposable fixture:

SET ROLE check_lab_migrator;

BEGIN;

ALTER TABLE lab.payments
    DROP CONSTRAINT payments_amount_nonnegative;

COMMIT;

After that commit, the CHECK's future-admission protection is gone. PostgreSQL documents DROP CONSTRAINT as removing the specified table constraint. Prove the consequence, rather than assuming it:

SET ROLE check_lab_app;

BEGIN;

INSERT INTO lab.payments(id, amount, note)
VALUES (30, -99, 'would be admitted after CHECK drop')
RETURNING *;

ROLLBACK;

That is a schema rollback demonstration, not an instruction to reverse the data remediation. Do not blindly run:

-- Do not treat this as an automatic rollback:
UPDATE lab.payments SET amount = -5 WHERE id = 2;

The prior value may have been wrong, or another authorized business event may have occurred since correction. Reversing business data requires its own approved mapping and expected-current-value guard. Where no such authorization exists, choose hold rather than manufacturing symmetry between DDL rollback and data rollback.

For the final accepted fixture, rerun setup or reinstall and validate the CHECK after the rollback demonstration. The terminal accepted state must be the installed enforced CHECK with convalidated=true, not the temporary reverted demonstration state.

Choose proceed-staged, remediate, validate, hold or revert from evidence

The migration decision should be mechanical enough that two reviewers reading the same evidence reach the same classification.

A successful VALIDATE CONSTRAINT is highly important but still not the entire acceptance packet. It proves that PostgreSQL completed validation of that constraint under the database state it observed. It does not show that the application role tested the expected error path, that the installed expression matches the business contract reviewers intended, or that a separate “amount must be non-null” requirement exists. PostgreSQL's catalog and CHECK documentation support precisely those distinctions.

The minimum closeout record should contain:

1.        The DDL revision and literal intended predicate: CHECK (amount >= 0).

2.        Exact SELECT version() and psql --version outputs.

3.        Database, schema, relation OID, constraint name and effective application/migration roles.

4.        Transaction isolation/read-only/deferrable settings and the lock_timeout/statement_timeout values used for each migration probe.

5.        Pre-install, installed-unvalidated, failed-validation and final catalog snapshots.

6.        The independent input ledger, approved remediation mapping and post-remediation ledger.

7.        Accepted positive and NULL writes plus rejected negative INSERT and UPDATE evidence, including actual SQLSTATEs and final persisted-state checks.

8.        The controlled blocking observation, including blocker identity, timeout classification and explicit recovery.

9.        The business authorization for changing legacy data.

10.    The final decision, technical owner, business-data owner and date.

These are evidence categories, not an invitation to manufacture outputs. A commissioning document can define the expected state, such as 23514 for the CHECK failures and convalidated=false before successful validation, but only an executed test can supply actual observations. PostgreSQL recommends applications identify error conditions by SQLSTATE rather than localized message text, which also makes captured SQLSTATE the better compatibility artifact.

Application handover is part of acceptance because the constraint alters which updates are admissible. Ownership should be explicit. The schema owner maintains the constraint definition and DDL revision. The business-data owner decides whether historical anomalies are corrections, valid exceptions, quarantines or deletions. Backend owners test every relevant write path, including updates that do not visibly edit amount, and ensure expected 23514 errors are handled appropriately rather than converted into silent retries. The operational owner investigates unexpected violations against the specific contract rather than treating every integrity error as generic database noise.

That division is an example of communication during database changes: the useful handover is not “DB constraint added,” but a record of what is prohibited, what NULL means, what legacy state was accepted, who can authorize corrections and what application behavior changed.

A compact handover can read:

Object:
  lab.payments
  payments_amount_nonnegative

Revision:
  check_amount_nonnegative_r1

Contract:
  known amount >= 0
  NULL explicitly permitted

Installed state:
  CHECK
  enforced = true
  validated = true

Application expectation:
  INSERT/UPDATE producing known negative amount => CHECK violation
  NULL amount => accepted by this contract

Legacy action:
  id=2, -5 -> 5
  authorization LAB-APPROVAL-001
  guarded old-value match captured

Owners:
  predicate / DDL: record the responsible team
  business data: record the responsible team
  application write paths: record the responsible team

Revalidation triggers:
  predicate change
  column semantic change
  application write-path change
  schema revision affecting lab.payments
  evidence that retained rows no longer match the accepted business contract

PostgreSQL also assumes CHECK conditions are effectively immutable for the same input row and warns about checks whose behavior can change through user-defined functions or references beyond the current row. This playbook deliberately uses only the row-local integer expression amount >= 0 and excludes user-defined functions inside the CHECK.

The boundary matters. Nothing here establishes a method for foreign-key rollout, partitioned or inherited relations, UNIQUE/index construction, replication behavior, triggers, or production zero-downtime guarantees. Those mechanisms carry different semantics, dependencies and locking questions.

Develop the operating foundations behind the acceptance test

The deeper skill in this exercise is not memorizing NOT VALID; it is keeping installation, enforcement, validation, business-data remediation, application compatibility and recovery as separately inspectable states.

For practitioners building those foundations, Refonte Learning's Database Administrator Essentials page currently describes a three-month program at 12–14 hours per week, recommends basic programming knowledge, and lists database design, SQL optimization, migration/integration, backup and recovery, security, monitoring and maintenance among its competencies. It also describes practical projects, guidance and potential internship experience, with a Training Certificate and Certificate of Internship listed on completion. Explicit PostgreSQL 18 instruction, a CHECK ... NOT VALID module, lock-state laboratories and this exact acceptance exercise are not established by that published page and should not be implied.

Finish only when the exact relation has the expected enforced, validated CHECK; approved retained rows reconcile; positive and NULL writes are admitted; known-negative row versions are rejected; the blocking probe is classified and recovered; and named owners retain the evidence.