On Wed, 20 Mar 2024 at 09:43, Peter Eisentraut <peter@eisentraut.org> wrote:
>
> On 19.03.24 10:57, jian he wrote:
> > this new syntax need to be added into the alter_domain.sgml's synopsis and also
> > need an explanation varlistentry?
>
> The ALTER DOMAIN reference page refers to CREATE DOMAIN about the
> details of the constraint syntax. I believe this is still accurate. We
> could add more detail locally on the ALTER DOMAIN page, but that is not
> this patch's job. For example, the details of CHECK constraints are
> also not shown on the ALTER DOMAIN page right now.
>
Hmm, for CHECK constraints, the ALTER DOMAIN syntax for adding a
constraint is the same as for CREATE DOMAIN, but that's not the case
for NOT NULL constraints. So, for example, these both work:
CREATE DOMAIN d AS int CONSTRAINT c1 CHECK (value > 0);
ALTER DOMAIN d ADD CONSTRAINT c2 CHECK (value < 10);
However, for NOT NULL constraints, the ALTER DOMAIN syntax differs
from the CREATE DOMAIN syntax, because it expects "NOT NULL" to be
followed by a column name. So the following CREATE DOMAIN syntax
works:
CREATE DOMAIN d AS int CONSTRAINT nn NOT NULL;
but the equivalent ALTER DOMAIN syntax doesn't work:
ALTER DOMAIN d ADD CONSTRAINT nn NOT NULL;
ERROR: syntax error at or near ";"
LINE 1: ALTER DOMAIN d ADD CONSTRAINT nn NOT NULL;
^
All the examples in the tests append "value" to this, presumably by
analogy with CHECK constraints, but it looks as though anything works,
and is simply ignored:
ALTER DOMAIN d ADD CONSTRAINT nn NOT NULL xxx; -- works
That doesn't seem particularly satisfactory. I think it should not
require (and reject) a column name after "NOT NULL".
Looking in the SQL spec, it seems to only mention adding CHECK
constraints to domains, so the option to add NOT NULL constraints
should probably be listed in the "Compatibility" section.
Regards,
Dean
