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.