Fix ALTER DOMAIN NOT NULL syntax

This addresses a few problems with commit e5da0fe3c22 ("Catalog domain
not-null constraints").

In CREATE DOMAIN, a NOT NULL constraint looks like

    CREATE DOMAIN d1 AS int [ CONSTRAINT conname ] NOT NULL

(Before e5da0fe3c22, the constraint name was accepted but ignored.)

But in ALTER DOMAIN, a NOT NULL constraint looks like

    ALTER DOMAIN d1 ADD [ CONSTRAINT conname ] NOT NULL VALUE

where VALUE is where for a table constraint the column name would be.
(This works as of e5da0fe3c22.  Before e5da0fe3c22, this syntax
resulted in an internal error.)

But for domains, this latter syntax is confusing and needlessly
inconsistent between CREATE and ALTER.  So this changes it to just

    ALTER DOMAIN d1 ADD [ CONSTRAINT conname ] NOT NULL

(None of these syntaxes are per SQL standard; we are just living with
the bits of inconsistency that have built up over time.)

In passing, this also changes the psql \dD output to not show not-null
constraints in the column "Check", since it's already shown in the
column "Nullable".  This has also been off since e5da0fe3c22.

Reviewed-by: jian he <jian.universality@gmail.com>
Discussion: https://www.postgresql.org/message-id/flat/9ec24d7b-633d-463a-84c6-7acff769c9e8%40eisentraut.org

2 files changed, 26 insertions, 8 deletions

diff --git a/src/test/regress/expected/domain.out b/src/test/regress/expected/domain.out
index fa8459e10ff..db0b8a180a3 100644
--- a/src/test/regress/expected/domain.out
+++ b/src/test/regress/expected/domain.out
@@ -785,6 +785,13 @@ alter domain con add constraint t check (VALUE < 1); -- fails
 ERROR:  column "col1" of table "domcontest" contains values that violate the new constraint
 alter domain con add constraint t check (VALUE < 34);
 alter domain con add check (VALUE > 0);
+\dD con
+                                         List of domains
+ Schema | Name |  Type   | Collation | Nullable | Default |                Check                 
+--------+------+---------+-----------+----------+---------+--------------------------------------
+ public | con  | integer |           |          |         | CHECK (VALUE < 34) CHECK (VALUE > 0)
+(1 row)
+
 insert into domcontest values (-5); -- fails
 ERROR:  value for domain con violates check constraint "con_check"
 insert into domcontest values (42); -- fails
@@ -805,26 +812,33 @@ create table domconnotnulltest
 , col2 connotnull
 );
 insert into domconnotnulltest default values;
-alter domain connotnull add not null value; -- fails
+alter domain connotnull add not null; -- fails
 ERROR:  column "col1" of table "domconnotnulltest" contains null values
 update domconnotnulltest set col1 = 5;
-alter domain connotnull add not null value; -- fails
+alter domain connotnull add not null; -- fails
 ERROR:  column "col2" of table "domconnotnulltest" contains null values
 update domconnotnulltest set col2 = 6;
-alter domain connotnull add constraint constr1 not null value;
+alter domain connotnull add constraint constr1 not null;
 select count(*) from pg_constraint where contypid = 'connotnull'::regtype and contype = 'n';
  count 
 -------
      1
 (1 row)
 
-alter domain connotnull add constraint constr1bis not null value;  -- redundant
+alter domain connotnull add constraint constr1bis not null;  -- redundant
 select count(*) from pg_constraint where contypid = 'connotnull'::regtype and contype = 'n';
  count 
 -------
      1
 (1 row)
 
+\dD connotnull
+                            List of domains
+ Schema |    Name    |  Type   | Collation | Nullable | Default | Check 
+--------+------------+---------+-----------+----------+---------+-------
+ public | connotnull | integer |           | not null |         | 
+(1 row)
+
 update domconnotnulltest set col1 = null; -- fails
 ERROR:  domain connotnull does not allow null values
 alter domain connotnull drop constraint constr1;
diff --git a/src/test/regress/sql/domain.sql b/src/test/regress/sql/domain.sql
index 763c68f1db6..b5a70ee8be7 100644
--- a/src/test/regress/sql/domain.sql
+++ b/src/test/regress/sql/domain.sql
@@ -458,6 +458,8 @@ alter domain con add constraint t check (VALUE < 1); -- fails
 alter domain con add constraint t check (VALUE < 34);
 alter domain con add check (VALUE > 0);
 
+\dD con
+
 insert into domcontest values (-5); -- fails
 insert into domcontest values (42); -- fails
 insert into domcontest values (5);
@@ -477,18 +479,20 @@ create table domconnotnulltest
 );
 
 insert into domconnotnulltest default values;
-alter domain connotnull add not null value; -- fails
+alter domain connotnull add not null; -- fails
 
 update domconnotnulltest set col1 = 5;
-alter domain connotnull add not null value; -- fails
+alter domain connotnull add not null; -- fails
 
 update domconnotnulltest set col2 = 6;
 
-alter domain connotnull add constraint constr1 not null value;
+alter domain connotnull add constraint constr1 not null;
 select count(*) from pg_constraint where contypid = 'connotnull'::regtype and contype = 'n';
-alter domain connotnull add constraint constr1bis not null value;  -- redundant
+alter domain connotnull add constraint constr1bis not null;  -- redundant
 select count(*) from pg_constraint where contypid = 'connotnull'::regtype and contype = 'n';
 
+\dD connotnull
+
 update domconnotnulltest set col1 = null; -- fails
 
 alter domain connotnull drop constraint constr1;