Message-ID: <d2d319cd-ddb9-a94a-7e2a-1879a6c52e64@jakobs.com>
Date: Fri, 29 Dec 2023 20:28:31 +0100
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.12.0
Content-Language: de-DE
To: "[PGSQL-ADMIN-LIST]" <pgsql-admin@postgresql.org>
From: Holger Jakobs <holger@jakobs.com>
Subject: Domain checks not always working when used in compound type
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/d2d319cd-ddb9-a94a-7e2a-1879a6c52e64%40jakobs.com>
Precedence: bulk

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!