From: Holger Jakobs <holger@jakobs.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: [PGSQL-ADMIN-LIST] <pgsql-admin@postgresql.org>
Subject: Re: Domain checks not always working when used in compound type
Date: Fri, 29 Dec 2023 20:56:12 +0100
Message-ID: <03181d25-d2ee-b638-a285-0986dee1ab60@jakobs.com> (raw)
In-Reply-To: <3236280.1703879366@sss.pgh.pa.us>
References: <d2d319cd-ddb9-a94a-7e2a-1879a6c52e64@jakobs.com>
<3236280.1703879366@sss.pgh.pa.us>
Am 29.12.23 um 20:49 schrieb Tom Lane:
> Holger Jakobs <holger@jakobs.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?
Regards,
Holger
--
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
Attachments:
[application/pgp-signature] OpenPGP_signature (202B, ../03181d25-d2ee-b638-a285-0986dee1ab60@jakobs.com/2-OpenPGP_signature)
download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: holger@jakobs.com, tgl@sss.pgh.pa.us
Subject: Re: Domain checks not always working when used in compound type
In-Reply-To: <03181d25-d2ee-b638-a285-0986dee1ab60@jakobs.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox