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.

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

For stable results you could always use: SELECT ROW(1, NULL, 'baz text') IS NOT DISTINCT FROM ROW(1, NULL, 'baz text'); — Lukasz Szozda
– Lukasz Szozda, Commented Dec 26, 2015 at 19:16
@lad2025 - I'm familiar with the IS NOT DISTINCT FROM clause; however, I'm interested in the = operator when used with composite types. — losthorse
– losthorse, Commented Dec 26, 2015 at 19:41
This is interesting, I guess it compares string representation Demo. So { "type": "test_type", "value": "(,,)" }={ "type": "test_type", "value": "(,,)" } => true — Lukasz Szozda
– Lukasz Szozda, Commented Dec 26, 2015 at 19:44
@lad2025 - wow... your second test (NULL,NULL, NULL)::test_type IS NULL Returns TRUE - I find that even more interesting as SELECT ARRAY[NULL] IS NULL Returns FALSE (not relevant to this question, just interesting). — losthorse
– losthorse, Commented Dec 26, 2015 at 19:54
This is described in doc 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, while IS NOT NULL is true when the row expression itself is non-null and all the row's fields are non-null. — Lukasz Szozda
– Lukasz Szozda, Commented Dec 26, 2015 at 19:56

Ezequiel Tolnay · Accepted Answer · 2016-03-31 07:15:33Z

4

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.

answered Mar 31, 2016 at 7:15

Ezequiel Tolnay

4,5821 gold badge21 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

wildplasser · Accepted Answer · 2015-12-26 21:46:47Z

0

Illustration (sorry, I cannot do this in a comment, need the formatting):

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

        -- plain table with three fields
CREATE TABLE test_table0 (
    foo INTEGER
    , bar BIGINT
    , baz TEXT
    );

        -- the same, but with a composite type
CREATE TABLE test_table1 (
        tt test_type
        );

INSERT INTO test_table0 (foo,bar,baz)
        VALUES (1, NULL, 'baz text');

INSERT INTO test_table1 (tt)
        VALUES( (1, NULL, 'baz text')::test_type) ;

        -- union needs a "whole row" -compare
SELECT * FROM test_table0
UNION
SELECT * FROM test_table0
        ;

        -- union needs a "whole row" -compare
        -- and type needs a whole "composite" compare
SELECT * FROM test_table1
UNION
SELECT * FROM test_table1
        ;

Result:

CREATE TYPE
CREATE TABLE
CREATE TABLE
INSERT 0 1
INSERT 0 1
 foo | bar |   baz    
-----+-----+----------
   1 |     | baz text
(1 row)

       tt        
-----------------
 (1,,"baz text")
(1 row)

Note: the objects that are compared (type instances and tuples) are not NULL, only some of their elements.
I do think this is the intended behaviour
IMHO this comes close to the problem with allowing NULL elements in composite keys, (see the rants by Chris Date about this)
probably the other behaviour would lead to even stranger artefacts

answered Dec 26, 2015 at 21:46

wildplasser

44.5k9 gold badges72 silver badges116 bronze badges

2 Comments

losthorse Over a year ago

I see what you are doing, but I'm not sure I understand it's implications... do you have any input on if this behavior should be trusted today, and also in the future?

wildplasser Over a year ago

At least the UNION construct relies on the comparison of whole rows, even if there are NULLs in there. And yes: some people rely on UNIONs to work as shown.

Collectives™ on Stack Overflow

PostgreSQL 9.4 - Comparing NULL values

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related