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
- v1-0001-ALTER-DOMAIN-ADD-NOT-NULL-NOT-VALID.patch (text/x-patch) patch v1-0001
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 →
-
Reduce lock level for ALTER DOMAIN ... VALIDATE CONSTRAINT
- 16a0039dc0d1 19 (unreleased) cited