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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Sat, Dec 30, 2023 at 3:56 AM Holger Jakobs <holger@xxxxxxxxxx> wrote:
>
> Am 29.12.23 um 20:49 schrieb Tom Lane:
> > Holger Jakobs <holger@xxxxxxxxxx> 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.






[Index of Archives]     [Postgresql Home]     [Postgresql General]     [Postgresql Performance]     [Postgresql PHP]     [Postgresql Jobs]     [PHP Users]     [PHP Databases]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Yosemite Forum]

  Powered by Linux