I’m trying to move from ILIKE to Postgres ~* operator. Tables have bpchar columns (cannot changed). Tried in Postgres 12
create temp table test ( tasudok char(25), nimetus char (100) ) on commit drop ;
insert into test values ('123','Teie Arve 123 tasumine');
select nimetus ~* '*. '||tasudok||' *.' from test;
but got error
ERROR: invalid regular expression: quantifier operand invalid
How to use substring match with ~* operator?
1
Your *. was probably supposed to be .* and it’s reversed for some reason. The leading * is expecting to be preceded by something, but it’s right at the start of the pattern, hence the error. Flip those and it’ll work just fine:
demo at db-fiddle
select nimetus ~* ('.* '||tasudok||' .*') from test;
9.7.3.1. Regular Expression Details has a note warning against just that:
A quantifier cannot immediately follow another quantifier, e.g.,
**is invalid. A quantifier cannot begin an expression or subexpression or follow^or|.
You also probably want to parenthesize the concatenated strings, otherwise they get evaluated left-to-right. As a result, only the first one’s matched, and the resulting boolean gets cast to text, then concatenated to tasudok, then to ' *.':
| nimetus | tasudok | operation | result |
|---|---|---|---|
| ‘Teie Arve 123 tasumine’ | ‘123’ | nimetus ~* ‘.* ‘||tasudok||’ .*’ | true123 .* |
| ‘Teie Arve 123 tasumine’ | ‘123’ | (nimetus ~* ‘.* ‘)||tasudok||’ .*’ | true123 .* |
| ‘Teie Arve 123 tasumine’ | ‘123’ | nimetus ~* (‘.* ‘||tasudok||’ .*’) | true |
| ‘Teie Arve 456 tasumine’ | ‘123’ | nimetus ~* ‘.* ‘||tasudok||’ .*’ | true123 .* |
| ‘Teie Arve 456 tasumine’ | ‘123’ | nimetus ~* (‘.* ‘||tasudok||’ .*’) | false |
Simply:
SELECT nimetus ~* tasudok FROM test;
You do not need to pad the pattern at all like you would have to with ILIKE. Equivalent 1:
SELECT nimetus ILIKE '%' || tasudok || '%' FROM test;
1 Except for special characters in the “pattern” column tasudok that would have different meaning. See:
- Escape function for regular expression or LIKE patterns
Operator precedence
Notably, ILIKE and LIKE rank one step below default operators in operator precedence, so no parentheses are required for my expression. Strictly speaking, LIKE is not an operator, but an SQL construct, that is implemented with the Postgres operator ~~ internally. (~~* for ILIKE, !~~ for NOT LIKE, !~~* for NOT ILIKE)
Also notably, those internal operators have default operator precedence (as do the regex operators ~, ~*, !~, and !~*). So parentheses are required around the concatenated string for those.
It’s just that you do not need to concatenate anything to begin with for your example. Would be expensive noise.