From: Holger Jakobs <holger@jakobs.com>
To: [PGSQL-ADMIN-LIST] <pgsql-admin@postgresql.org>
Subject: Domain checks not always working when used in compound type
Date: Fri, 29 Dec 2023 20:28:31 +0100
Message-ID: <d2d319cd-ddb9-a94a-7e2a-1879a6c52e64@jakobs.com> (raw)
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!
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
Subject: Re: Domain checks not always working when used in compound type
In-Reply-To: <d2d319cd-ddb9-a94a-7e2a-1879a6c52e64@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