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

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

 



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?

Regards,

Holger


--

Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012

Attachment: OpenPGP_signature
Description: OpenPGP digital signature


[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