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.