pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedDomain checks not always working when used in compound type
4+ messages / 3 participants
[nested] [flat]
* Domain checks not always working when used in compound type
@ 2023-12-29 19:28 Holger Jakobs <holger@jakobs.com>
0 siblings, 1 reply; 4+ messages in thread
From: Holger Jakobs @ 2023-12-29 19:28 UTC (permalink / raw)
To: pgsql-admin
Hello All,
Today I was experimenting with domain and compound types.
-- Creating a domain type with NOT NULL constraint and at least 2
characters.
CREATE DOMAIN domain_a AS VARCHAR(10) NOT NULL
CHECK (LENGTH(TRIM(value)) > 2);
-- 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)
);
-- Creating a table using the compound type.
CREATE TABLE test1 (
name VARCHAR(25) NOT NULL UNIQUE,
ab compound_ab,
id INTEGER GENERATED ALWAYS AS IDENTITY
);
-- Insert into table without providing a value for ab, thus (ab).a is
NULL, which shouldn't be allowed.
INSERT INTO test1 VALUES ('person1');
INSERT 0 1
-- Select proves that (ab).a is NULL - violating the NOT NULL constraint.
SELECT *, (ab).a FROM test1;
name | a
---------+---
person1 | ¤
¤ is set as a marker for NULL values in order to make it distinguishable
from an empty string.
-- Directly inserting a NULL gives an error, so the constraint is in place.
INSERT INTO test1 (name, ab.a, ab.b)
VALUES ('person2', NULL, 'something');
ERROR 23502: domain domain_a doesn't allow NULL values
INSERT INTO test1
VALUES ('person3', '(,something)');
ERROR 23502: domain domain_a doesn't allow NULL values
-- But it can be circumvented by only feeding ab.b, but not ab.a.
INSERT INTO test1 (name, ab.b)
VALUES ('person3', 'something');
SELECT *, (ab).a FROM test1;
name | ab | id | a
---------+--------------+----+---
person1 | ¤ | 1 | ¤
person3 | (,something) | 2 | ¤
The tests have been carried out with PostgreSQL 16.1.
Is the behaviour as expected? I think it isn't.
Regards,
Holger
--
Holger Jakobs, Bergisch Gladbach, Germany, Tel. +49-178-9759012
Hint: Only my mail server is entitled to send mails from my mail address. The SPF regulation is strict.
Mailing lists must not use the original from address when forwarding!
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Domain checks not always working when used in compound type
@ 2023-12-29 19:49 Tom Lane <tgl@sss.pgh.pa.us>
parent: Holger Jakobs <holger@jakobs.com>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2023-12-29 19:49 UTC (permalink / raw)
To: Holger Jakobs <holger@jakobs.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Domain checks not always working when used in compound type
@ 2023-12-29 19:56 Holger Jakobs <holger@jakobs.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Holger Jakobs @ 2023-12-29 19:56 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Domain checks not always working when used in compound type
@ 2023-12-30 04:51 jian he <jian.universality@gmail.com>
parent: Holger Jakobs <holger@jakobs.com>
0 siblings, 0 replies; 4+ messages in thread
From: jian he @ 2023-12-30 04:51 UTC (permalink / raw)
To: Holger Jakobs <holger@jakobs.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin
On Sat, Dec 30, 2023 at 3:56 AM Holger Jakobs <holger@jakobs.com> wrote:
>
> 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?
>
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2023-12-30 04:51 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-12-29 19:28 Domain checks not always working when used in compound type Holger Jakobs <holger@jakobs.com>
2023-12-29 19:49 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-12-29 19:56 ` Holger Jakobs <holger@jakobs.com>
2023-12-30 04:51 ` jian he <jian.universality@gmail.com>
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