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!