ALTER DOMAIN ADD NOT NULL NOT VALID

From: jian he <jian(dot)universality(at)gmail(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: ALTER DOMAIN ADD NOT NULL NOT VALID
Date: 2025-05-21 10:44:18
Message-ID: CACJufxGcABLgmH951SJkkihK+FW8KR3=odBhXEVCF9atQbur2Q@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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)

Attachment Content-Type Size
v1-0001-ALTER-DOMAIN-ADD-NOT-NULL-NOT-VALID.patch text/x-patch 17.2 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Fujii Masao 2025-05-21 12:03:09 Re: Allow reading LSN written by walreciever, but not flushed yet
Previous Message shveta malik 2025-05-21 10:41:22 Re: Logical Replication of sequences