postgres.git / summary / log / commit / refs

commit    7b1053a577494871acb5e91c63abb2bade5b8dea
Author:   Álvaro Herrera <alvherre@kurilemu.de>
Date:     Mon Aug 04 11:26:45 2025 +0000
First released in: 13.22, 14.19, 15.14, 16.10, 17.6, 18.0

    doc: mention unusability of dropped CHECK to verify NOT NULL
    
    It's possible to use a CHECK (col IS NOT NULL) constraint to skip
    scanning a table for nulls when adding a NOT NULL constraint on the same
    column.  However, if the CHECK constraint is dropped on the same command
    that the NOT NULL is added, this fails, i.e., makes the NOT NULL addition
    slow.  The best we can do about it at this stage is to document this so
    that users aren't taken by surprise.
    
    (In Postgres 18 you can directly add the NOT NULL constraint as NOT
    VALID instead, so there's no longer much use for the CHECK constraint,
    therefore no point in building mechanism to support the case better.)
    
    Reported-by: Andrew <psy2000usa@yahoo.com>
    Reviewed-by: David G. Johnston <david.g.johnston@gmail.com>
    Discussion: https://postgr.es/m/175385113607.786.16774570234342968908@wrigleys.postgresql.org


 doc/src/sgml/ref/alter_table.sgml | 7 ++++---
 1 file changed, 4 insertions(+), 3 deletions(-)

diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index d1696991683..3bac4db7422 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -240,9 +240,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
       provided none of the records in the table contain a
       <literal>NULL</literal> value for the column.  Ordinarily this is
       checked during the <literal>ALTER TABLE</literal> by scanning the
-      entire table; however, if a valid <literal>CHECK</literal> constraint is
-      found which proves no <literal>NULL</literal> can exist, then the
-      table scan is skipped.
+      entire table, unless <literal>NOT VALID</literal> is specified;
+      however, if a valid <literal>CHECK</literal> constraint exists
+      (and is not dropped in the same command) which proves no
+      <literal>NULL</literal> can exist, then the table scan is skipped.
       If a column has an invalid not-null constraint,
       <literal>SET NOT NULL</literal> validates it.
      </para>


[parent: fee46ab4f244]