Difference between null composite type and composite type with all null columns

Question

Is there any difference between a null value and a row type where all columns are null? Postgres queries appear to be able to tell the difference (displaying null columns rather than a blank) and I want to know if there's anything I should be aware of. e.g.

CREATE TYPE node AS (
   rank integer
 , id integer
);

CREATE TABLE hierarchy (
   node node
);

INSERT INTO hierarchy (node) VALUES (null);
INSERT INTO hierarchy (node) VALUES ((null, null));

SELECT *, node IS NULL AS check_null FROM hierarchy;

 node | check_null
------+------------
      | t
 (,)  | t

Answer 1

Is there any difference between a null value and a rowtype where all the columns are null?

NULL:node is still distinct from (null, null)::node:

SELECT null::node IS DISTINCT FROM (null, null)::node AS dist;

dist
----
t

I agree this is confusing. And the manual might be sharpened here, too:

For non-null inputs, IS DISTINCT FROM is the same as the <> operator. However, if both inputs are null it returns false, and if only one input is null it returns true.

Turns out to be slightly incorrect in the face of above demo. Even though there is a hint further down:

If the expression is row-valued, then IS NULL is true when the row expression itself is null or when all the row's fields are null.

So we have two distinct cases in which a row value is null:

1. expression itself is null.
2. all the row's fields are null.

It should be mentioned that these two cases are still considered distinct, when compared with IS DISTINCT FROM. May be worth a documentation bug report ...

anything I should be aware of?

Yes. Wherever DISTINCT comes into play, both variants are considered, well, distinct:

SELECT DISTINCT * FROM hierarchy;

 node1
------
 (,)  

(2 rows)

Note the 2nd invisible row, because psql displays null values this way.

To stop the (,) case from occurring ?

Addressing your comment:

CREATE TABLE hierarchy (
  node node CHECK (node IS DISTINCT FROM (null, null)::node)
);

Note two things:

1. The explicit cast (null, null)::node is necessary.
2. Simple NULL values are still allowed, only a row with all null values violates the constraint.