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?
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 |