Re: Support NOT VALID / VALIDATE constraint options for named NOT NULL constraints

On Thu, Mar 20, 2025 at 5:54 PM Rushabh Lathia <rushabh(dot)lathia(at)gmail(dot)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>