Re: BUG #18451: NULL fails to coerce to string when performing string comparison

On Monday, April 29, 2024, PG Bug reporting form <noreply(at)postgresql(dot)org>
wrote:

> The following bug has been logged on the website:
>
> Bug reference: 18451
> Logged by: Darryl Dixon
> Email address: darryl(dot)dixon(at)gmail(dot)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.