Why does PostgreSQL allow NULLs in domains that prohibit NULL?

Question

PostgreSQL allows NULLs in domains marked as NOT NULL. Why is this, the docs say this about it,

It is possible for a column that is nominally of the domain type to read as null despite there being such a constraint. For example, this can happen in an outer-join query, if the domain column is on the nullable side of the outer join.

Can this be better explained?

Answer 1

Well, for starters look at this,

CREATE DOMAIN mydomain AS int;

CREATE TABLE foo(bar) AS SELECT 42::mydomain;

SELECT f1.bar AS f1, f2.bar AS f2, pg_typeof(f1.bar), pg_typeof(f2.bar)
FROM foo AS f1
LEFT JOIN foo AS f2
  ON false;

That currently returns,

 f1 | f2 | pg_typeof | pg_typeof 
----+----+-----------+-----------
 42 |    | mydomain  | mydomain

But what should it return? You're joining two tables and the only type in that table is mydomain which allows NULLs. Logically both columns returned should be of type mydomain. Now if you go back and run this,

CREATE DOMAIN mydomain AS int NOT NULL;

Should that return something different given the above commands? The answer is no -- for the same reason. The resultset is composed of two columns sourced from tables. If this is to return the same types as the source tables, like the above query, then it must return, however counter intuitively, a resultset with NULL. Note this isn't about INSERT it's just showing you that no matter what the type SQL can make it return NULL. That's how you get NULLs into a DOMAIN when the domain itself does not support NULL.

Possible Solutions

Four-valued Logic

How can you solve this the right way? You could graduate from Three-valued_logic (3vl) (which is true/false/null), to 4vl (which would be something like true/false/null/unknown). This would give SQL a primitive not available to the user that EVERYTHING would be parameterized over. It would look like,

// Definitions                              // Value Constructors on 

// Two-value
type Bool = True | False;                   // True | False

// Three-value
type NonNullable<T> = T;                    // True | False
type Nullable<T> = Null | NonNullablle<T>;  // Null | True | False

// Four-value!
type Option<T> = Unknown | Nullable<T>;     // So over every column 
type Option<T> = Unknown | NonNullable<T>;  // you can store Unknown

And, then every column returned by SQL as a result of a SELECT could be Option-ally Unknown a value the user could not choose and that every type must permit. That would solve this problem. It would also make SQL far harder to teach and grok. Imagine a four-value truth table.

Fatal SELECT

From Paul White another solution would be to make SELECT statements throw a fatal error if the value can't be coerced to or represented by the domain. Some SELECT statements already throw an error, like SELECT 1/0 or SELECT CAST('foo' AS int);

---

• Self-answered to preserve this conversation in chat