How can the concatenatation of a text and a NULL value return a non-null result?

Question

Assert 1

The concatenation operator || can concatenate any string type values, returning text. In Postgres, every type has a text representation and can be cast to text. Consequently, quoting the manual:

However, the string concatenation operator (||) still accepts non-string input, so long as at least one input is of a string type

Related:

• How to concatenate columns in a Postgres SELECT?

Assert 2

Concatenating one or more NULL values makes the result NULL.

test=# SELECT (text 'foo' || NULL) IS NULL
test-#      , (text 'bar' || char '1' || NULL ) IS NULL
test-#      , (NULL::bigint || text 'baz') IS NULL;
 ?column? | ?column? | ?column? 
----------+----------+----------
 t        | t        | t

Question

Is it possible to concatenate a text and a NULL value and get a non-null result?

In other words, how is this possible?

test=# SELECT col IS NULL AS col_is_null
test-#     , (text 'foo' || col) IS NULL AS result_is_null
test-# FROM   tbl;
 col_is_null | result_is_null 
-------------+----------------
 t           | f

Applies to any Postgres version.
A client of mine stumbled over this, relying on the result to be NULL, and I found it intriguing enough to share.
It's a bit of a trick question as I know the answer.

_{Note: CASE or COALESCE catching NULL values are typically good style, but that's not what this question is about. It's about concatenation with an actual NULL value, with the concatenation operator || and still getting a non-null result.}

Answer 1

That's because the system of CASTing array types in PostgreSQL is a bit weird (on a first sight) :-)

text || text[] makes both sides coerced to arrays.

CREATE TABLE tbl (col text ARRAY);

INSERT INTO tbl SELECT NULL;

SELECT col IS NULL AS col_is_null,
  (text 'foo' || col) IS NULL AS result_is_null
  FROM tbl;

 col_is_null | result_is_null 
-------------+----------------
 t           | f
(1 row)

Another example which may shed more light:

create temp table x as select 'foo' test, null::text[] col; 
SELECT test, col, test || col from x;
 test | col  | ?column? 
------+------+----------
 foo  | NULL | {foo}
(1 row)

Answer 2

Question Is it possible to concatenate a text and a NULL value and get a non-null result?

Answer, yes!

Using nested CASE statements (with thanks to Craig Ringer):

CREATE TABLE null_test
(
  f1 TEXT,
  f2 INTEGER

);

INSERT INTO null_test VALUES ('foo', null);
INSERT INTO null_test VALUES (null, null);

Query:

SELECT 
  '*_' || COALESCE(f1, '') || '_*' AS "Check",
  '*_' || 
  CASE 
    WHEN f2 IS NULL THEN
      CASE
        WHEN COALESCE(f2, 0) = 0 THEN ''
      END
   END || '_*' AS test_1,
  '*_' ||  
  COALESCE(f1, '') ||
  CASE 
    WHEN f2 IS NULL THEN
      CASE
        WHEN COALESCE(f2, 0) = 0 THEN ''
      END
   END || '_*' AS test_2,
   '*_' ||
   LENGTH
   (    
     COALESCE(f1, '') ||
     CASE 
       WHEN f2 IS NULL THEN
         CASE
           WHEN COALESCE(f2, 0) = 0 THEN ''
         END
     END
   ) || '_*' AS test_3     
FROM null_test;

Result (the '*_ and '_*' are to make it more readily apparent what's going on!):

Check   test_1  test_2  test_3
*_foo_* *__*    *_foo_* *_3_*
*__*    *__*    *__*    *_0_*