Return Types In PSQL Pattern Match
If you use the pattern match operators in PSQL, you'll want to mind the column types passed to these statements
If you use a string, you will get a boolean return -
select 'a' like '%b%';
?column?
----------
f
(1 row)
select 'a' like '%a%';
?column?
----------
t
(1 row)But if you select a null in one of these statements, the return is null as well -
select null like '%a%';
?column?
----------
ΓΈ
(1 row)Moral of the story - if you're expecting a boolean, you can coalesce the column before the pattern match -
select coalesce(null, '') like '%a%';
?column?
----------
f
(1 row)https://www.postgresql.org/docs/current/functions-matching.html
Tweet