2

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:

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.

4
  • 1
    is 'col' a string column?
    – SQLRaptor
    Mar 29, 2019 at 15:32
  • @SQLRaptor: Good question. :) Turned out it's not a string type ... Mar 29, 2019 at 15:40
  • 1
    I guess one of the non-standard types causes this behavior, but have no time to check :-)
    – SQLRaptor
    Mar 29, 2019 at 17:41
  • Getting warm. :) It's really a "standard" type, though (as in: defined in the SQL standard). Mar 29, 2019 at 17:46

2 Answers 2

7

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)
3
0

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_*
3
  • Hmm, can't see a concatenation with NULL returning not-null here. (One can replace NULL with COALESCE or catch it with a CASE expression, but that's not what this question is about.) Mar 29, 2019 at 18:02
  • How about this? If I"m on the right track, I can write it up!
    – Vérace
    Mar 29, 2019 at 18:14
  • Nay, still cold! :) Mar 29, 2019 at 18:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.