PostgreSQL 9.4 - Comparing NULL values

Question

Under normal conditions, comparing a NULL value to any other value results in another NULL value.

SELECT NULL = NULL;

Returns NULL

---

This holds (mostly) true when comparing arbitrary rows, as explained in the documentation, 9.23.5. Row Constructor Comparison:

SELECT ROW(1, NULL, 'baz text') = ROW(1, NULL, 'baz text'); 

Returns NULL

---

However, when comparing well defined composite types, NULL values are treated as equal.

CREATE TYPE test_type AS (
    foo INTEGER,
    bar BIGINT,
    baz TEXT
);

SELECT (1, NULL, 'baz text')::test_type = (1, NULL, 'baz text')::test_type;

Returns TRUE

This behavior seems to be undocumented (I have looked and found no reference to the behavior).

I would like to use this behavior to implement some business rules and want to make sure it is safe to do so.

1. Is this consistent with any SQL specification?
2. Is it likely this will change in the future?

Answer 1

I found this in the official documentation: [...] In other contexts where two composite-type values are compared, two NULL field values are considered equal, and a NULL is considered larger than a non-NULL. This is necessary in order to have consistent sorting and indexing behavior for composite types.. I think this resolves your question.