4

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
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
dbeacham
  • 986
  • 2
  • 9
  • 16
  • Check your database design, because a row where all columns are NULL has not sense in the relational model. You always need a unique primary key to identify each row in a table. A row can have some NULL fields, but the primary key of a row never shouldn't be NULL. – aicastell Apr 03 '17 at 14:34
  • @aicastell Read rowtype in the question as composite type. – Clodoaldo Neto Apr 03 '17 at 14:39
  • 1
    Your question has numerous syntax errors (and semantic errors as well, f.ex. node is both a composite type & a table name). Are you sure you ran those? http://rextester.com/ZMOO91035 – pozs Apr 03 '17 at 15:01
  • The weird behavior of `(NULL, NULL) IS NULL` comes from the SQL standard. But actually it is quite handy, when dealing with `OUTER JOIN`s. – pozs Apr 03 '17 at 15:04
  • What you show is impossible. A table and a composite type cannot have the same name. Please fix your question to make sense. And *always* declare the Postgres version you are working with. – Erwin Brandstetter Apr 03 '17 at 16:00
  • @posz Thanks - tidied up and now makes sense. – dbeacham Apr 03 '17 at 17:58

1 Answers1

7

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.
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228