Re: Domain checks not always working when used in compound type

On Sat, Dec 30, 2023 at 3:56 AM Holger Jakobs <holger(at)jakobs(dot)com> wrote:
>
> Am 29.12.23 um 20:49 schrieb Tom Lane:
> > Holger Jakobs <holger(at)jakobs(dot)com> writes:
> >> -- Using the domain within a compound type. The NOT NULL constraint
> >> should be
> >> -- inherited from the domain, therefore not repeated here for column a.
> >> CREATE TYPE compound_ab AS (
> >> a domain_a,
> >> b varchar(10)
> >> );
> > You are assuming in effect that a simple NULL value for a composite
> > type is the same thing as ROW(NULL, NULL). They are not quite the
> > same, and one way in which they are not is that we don't consider
> > field-level constraints when deciding if a simple NULL value is legal
> > for the composite -- it always is. Thus
> >
> > regression=# select null::compound_ab;
> > compound_ab
> > -------------
> >
> > (1 row)
> >
> > regression=# select row(null, null)::compound_ab;
> > ERROR: domain domain_a does not allow null values
> >
> > The SQL spec itself is pretty schizophrenic about whether ROW(NULL, NULL)
> > is equivalent to bare NULL. This is how we've chosen to interpret it.
> > I'll freely admit that there's some implementation considerations
> > involved in that choice, but we're unlikely to revisit it.
> >
> > If you don't want things to work like this, you could attach a NOT
> > NULL constraint to the test1.ab column (as well as having the domain
> > constraint).
> >
> > regards, tom lane
>
> CREATE TABLE test1 (
> name VARCHAR(25) NOT NULL UNIQUE,
> ab compound_ab NOT NULL,
> id INTEGER GENERATED ALWAYS AS IDENTITY
> );
>
> INSERT INTO test1 VALUES ('person1');
> INSERT 0 1
> ERROR 23502: NULL value in column "ab" of relation "test1" violates NOT
> NULL constraint
>
> That one is fine now. Thanks for the explanation.
>
>
> OTOH, the second problem persists.
>
> INSERT INTO test1 (name, ab.b)
> VALUES ('person3', 'something');
> INSERT 0 1
>
> SELECT *, (ab).a FROM test1;
> name | ab | id | a
> ---------+--------------+----+---
> person3 | (,something) | 2 | ¤
>
> Can something be done about this one?
>

CREATE DOMAIN domain_a AS VARCHAR(10) NOT NULL CHECK (LENGTH(TRIM(value)) > 2);

CREATE TYPE compound_ab AS (a domain_a,b varchar(10));

CREATE TABLE test1 (
name VARCHAR(25) NOT NULL UNIQUE,
ab compound_ab,
id INTEGER GENERATED ALWAYS AS IDENTITY,
CONSTRAINT compound_ab_a CHECK ((ab).a IS NOT NULL),
CONSTRAINT compound_ab_b CHECK ((ab).b IS NOT NULL));

This should be fine.