ALTER DOMAIN ADD NOT NULL NOT VALID

jian he <jian.universality@gmail.com>

From: jian he <jian.universality@gmail.com>
To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2025-05-21T10:44:18Z
Lists: pgsql-hackers

Attachments

hi.

attached patch is for $subject implementation

per https://www.postgresql.org/docs/current/sql-alterdomain.html
"""
Although ALTER DOMAIN ADD CONSTRAINT attempts to verify that existing stored
data satisfies the new constraint, this check is not bulletproof, because the
command cannot “see” table rows that are newly inserted or updated and not yet
committed. If there is a hazard that concurrent operations might insert bad
data, the way to proceed is to add the constraint using the NOT VALID option,
commit that command, wait until all transactions started before that commit have
finished, and then issue ALTER DOMAIN VALIDATE CONSTRAINT to search for data
violating the constraint.
"""

Obviously, the above behavior can also happen to not-null constraints.
add NOT NULL NOT VALID is good for validation invalid data too.

the not valid information is displayed at column "Nullable"
for example:

\dD things
                                      List of domains
 Schema |  Name  |  Type   | Collation |      Nullable      | Default
|       Check
--------+--------+---------+-----------+--------------------+---------+--------------------
 public | things | integer |           | not null not valid |
| CHECK (VALUE < 11)

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →
  1. Reduce lock level for ALTER DOMAIN ... VALIDATE CONSTRAINT