regex – How to use posix regexp operator for substring match

Simply:

SELECT nimetus ~* tasudok FROM test;

You do not need to pad the pattern at all like you would have to with ILIKE. Equivalent ¹:

SELECT nimetus ILIKE '%' || tasudok || '%' FROM test;

¹ Except for special characters in the “pattern” column tasudok that would have different meaning. See:

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.