Re: Support NOT VALID / VALIDATE constraint options for named NOT NULL constraints - Mailing list pgsql-hackers

On Thu, Mar 20, 2025 at 5:54 PM Rushabh Lathia <rushabh.lathia@gmail.com> wrote:
>
hi. looking at the regress tests.

+-- verify NOT NULL VALID/NOT VALID
+CREATE TABLE notnull_tbl1 (a INTEGER, b INTEGER);
+INSERT INTO notnull_tbl1 VALUES (NULL, 1);
+INSERT INTO notnull_tbl1 VALUES (NULL, 2);
+INSERT INTO notnull_tbl1 VALUES (300, 3);
+-- Below statement should throw an error
+ALTER TABLE notnull_tbl1 ADD CONSTRAINT nn NOT NULL a;
+ERROR:  column "a" of relation "notnull_tbl1" contains null values
+ALTER TABLE notnull_tbl1 ADD CONSTRAINT nn NOT NULL a NOT VALID;
+\d+ notnull_tbl1
+                               Table "public.notnull_tbl1"
+ Column |  Type   | Collation | Nullable | Default | Storage | Stats
target | Description
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ a      | integer |           | not null |         | plain   |              |
+ b      | integer |           |          |         | plain   |              |
+Not-null constraints:
+    "nn" NOT NULL "a" NOT VALID

as I mentioned in an offline discussion.
as you can see the output of `\d+ notnull_tbl1`
That means the pg_attribute.attnotnull definition is changed.

current description in
https://www.postgresql.org/docs/current/catalog-pg-attribute.html
attnotnull bool
This represents a not-null constraint.

now the "attnotnull" column means:
This represents a not-null constraint, it may not be validated.
This attribute column may already contain NULLs on it.


so doc/src/sgml/catalogs.sgml the following part will need to adjust
accordingly.

     <row>
      <entry role="catalog_table_entry"><para role="column_definition">
       <structfield>attnotnull</structfield> <type>bool</type>
      </para>
      <para>
       This column has a not-null constraint.
      </para></entry>
     </row>