Re: domain check constraint should also consider domain's collation

jian he <jian(dot)universality(at)gmail(dot)com> writes:
> CREATE COLLATION case_insensitive (provider = icu, locale =
> '@colStrength=secondary', deterministic = false);
> SELECT 'a' = 'A' COLLATE case_insensitive;
> CREATE DOMAIN d1 as text collate case_insensitive check (value <> 'a');
> SELECT 'A'::d1;

> ``SELECT 'A'::d1`` should error out as domain check constraint not satisfied?

No. In the above, 'value' is of type text, not type d1, and therefore
that comparison will use the default collation. If you try to make it
do something else, you will break far more than you fix. (The
fundamental reason why this is important is that we cannot assume that
the domain constraints hold for the value until after we complete the
CHECK expressions.) So the correct way to create a domain that works
as you have in mind is

CREATE DOMAIN d1 as text collate case_insensitive
check (value <> 'a' COLLATE case_insensitive);

regards, tom lane