On Fri, 22 Mar 2024 at 08:28, jian he <jian.universal...@gmail.com> wrote: > > On Thu, Mar 21, 2024 at 7:23 PM Peter Eisentraut <pe...@eisentraut.org> wrote: > > > > Hmm. CREATE DOMAIN uses column constraint syntax, but ALTER DOMAIN uses > > table constraint syntax. Attached is a patch to try to sort this out. > > also you should also change src/backend/utils/adt/ruleutils.c? > > src6=# \dD > List of domains > Schema | Name | Type | Collation | Nullable | Default | > Check > --------+-------------+---------+-----------+----------+---------+---------------------------------- > public | domain_test | integer | | not null | | > CHECK (VALUE > 0) NOT NULL VALUE > (1 row) > > probably change to CHECK (VALUE IS NOT NULL)
I'd say it should just output "NOT NULL", since that's the input syntax that created the constraint. But then again, why display NOT NULL constraints in that column at all, when there's a separate "Nullable" column? Also (not this patch's fault), psql doesn't seem to offer a way to display domain constraint names -- something you need to know to drop or alter them. Perhaps \dD+ could be made to do that? + The syntax <literal>NOT NULL</literal> in this command is a + <productname>PostgreSQL</productname> extension. (A standard-conforming + way to write the same would be <literal>CHECK (VALUE IS NOT + NULL)</literal>. However, per <xref linkend="sql-createdomain-notes"/>, + such constraints a best avoided in practice anyway.) The + <literal>NULL</literal> <quote>constraint</quote> is a + <productname>PostgreSQL</productname> extension (see also <xref + linkend="sql-createtable-compatibility"/>). I didn't verify this, but I thought that according to the SQL standard, only non-NULL values should be passed to CHECK constraints, so there is no standard-conforming way to write a NOT NULL domain constraint. FWIW, I think NOT NULL domain constraints are a useful feature to have, and I suspect that there are more people out there who use them and like them, than who care what the SQL standard says. If so, I'm in favour of allowing them to be named and managed in the same way as NOT NULL table constraints. + processCASbits($5, @5, "CHECK", + NULL, NULL, &n->skip_validation, + &n->is_no_inherit, yyscanner); + n->initially_valid = !n->skip_validation; + /* no NOT VALID support yet */ + processCASbits($3, @3, "NOT NULL", + NULL, NULL, NULL, + &n->is_no_inherit, yyscanner); + n->initially_valid = true; NO INHERIT is allowed for domain constraints? What does that even mean? There's something very wonky about this: CREATE DOMAIN d1 AS int CHECK (value > 0) NO INHERIT; -- Rejected ERROR: check constraints for domains cannot be marked NO INHERIT CREATE DOMAIN d1 AS int; ALTER DOMAIN d1 ADD CHECK (value > 0) NO INHERIT; -- Allowed CREATE DOMAIN d2 AS int NOT NULL NO INHERIT; -- Now allowed (used to syntax error) CREATE DOMAIN d3 AS int; ALTER DOMAIN d3 ADD NOT NULL NO INHERIT; -- Allowed Presumably all of those should be rejected in the grammar. Regards, Dean
