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
Concatenating one or more
NULL values makes the result
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
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.
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.