Hi,
There are a couple of notes regarding this:
1) There is an explicit CAST of NULL->text that silently fails (see original example)
2) This behaviour, if expected, is not well documented/contradicted by existing documentation. See the note here:
"Note
The string concatenation operator (||) will accept non-string input, so long as at least one input is of string type, as shown in Table 9.9. For other cases, inserting an explicit coercion to text can be used to have non-string input accepted."
Further, the extended details in the referenced table say:
"text || anynonarray → text
anynonarray || text → text
Converts the non-string input to text, then concatenates the two strings. (The non-string input cannot be of an array type, because that would create ambiguity with the array || operators. If you want to concatenate an array's text equivalent, cast it to text explicitly.)
'Value: ' || 42 → Value: 42"
regards,
Darryl Dixon
On Monday, April 29, 2024, PG Bug reporting form <noreply@postgresql.org> wrote:
The following bug has been logged on the website:
Bug reference: 18451
Logged by: Darryl Dixon
Email address: darryl.dixon@gmail.com
PostgreSQL version: 14.11
Operating system: Ubuntu 22.04 64Bit
Description:
postdb=# select where 'ab' like '%a%';
--
(1 row)
postdb=# select where 'ab'||NULL like '%a%';
--
(0 rows)
postdb=# select where 'ab'||NULL::text like '%a%';
--
(0 rows)
You are mistaken in believing that concatenation involving null produces a non-null result. It is rather an inherently “strict” operation.
postdb=# select where 'ab'||format('%s', NULL::text) like '%a%';
--
(1 row)
Whereas format() is not “strict” and you choice of %s results in the documented empty string substitution.
David J.