Can one define a NULLable composite type whose fields are NOT NULL?

Question

I would like to define a composite type whose fields are NOT NULL; while at the same time, allow the value itself to be NULL in a table column. My first attempt was to define a DOMAIN on the composite type with a CHECK constraint that ensured the fields are NOT NULL; unfortunately this prevents NULL itself from being INSERTed into the table:

BEGIN;
 CREATE TYPE foo AS (x int, y int);
 CREATE DOMAIN non_null_foo AS foo CHECK((VALUE).x IS NOT NULL AND (VALUE).y IS NOT NULL);
 CREATE TABLE bar(y non_null_foo);
 INSERT INTO bar VALUES (NULL);
ROLLBACK;

errors: ERROR: value for domain non_null_foo violates check constraint "non_null_foo_check".

My second attempt was to allow NULL for VALUE in the DOMAIN, but this also does not work since it now allows a value where all fields are NULL:

BEGIN;
 CREATE TYPE foo AS (x int, y int);
 CREATE DOMAIN non_null_foo AS foo CHECK(VALUE IS NULL OR ((VALUE).x IS NOT NULL AND (VALUE).y IS NOT NULL));
 CREATE TABLE bar(y non_null_foo);
 INSERT INTO bar VALUES ((NULL, NULL)); --succeeds
 INSERT INTO bar VALUES ((1, NULL)); --fails
ROLLBACK;

It's as if Postgresql is unable to distinguish between NULL and a value where all the fields are NULL. Is there something I'm missing?

Answer 1

Create a DOMAIN over integer that disallows NULL first.
Then create a composite TYPE using that DOMAIN:

BEGIN;
CREATE DOMAIN int_notnull AS int NOT NULL);
CREATE TYPE foo_nonulls AS (x int_notnull, y int_notnull);
CREATE TABLE bar(z foo_nonulls);
INSERT INTO bar VALUES (NULL); -- OK
INSERT INTO bar VALUES ('(1,2)'); -- OK
INSERT INTO bar VALUES ('(1,)'); -- ERROR: value for domain int_notnull violates check constraint "int_notnull_check"
ROLLBACK;

fiddle

This way you can disallow nulls in any or all fields of the composite type, but still allow null for the composite as a whole.

• philomathic_life: Embarrassed I did not think about trying that. I'm so glad you answered my question because you're a freakin' god when it comes to Postgresql. I'm quite new to Postgresql, but I am pretty comfortable with SQL Server—not Paul White level of course. Hope to one day have a fraction of your knowledge. Thanks.
• Erwin Brandstetter: @philomathic_life Thanks for the flattery, we are all mere mortals. The first 80 % are to be had quickly, that's the charm of starting something new.