What is the advantage of using POSIX regular expressions over LIKE and ILIKE in PostgreSQL?
The regex / like operators require something in the DB, so of course they affect performance in some way ... but they all do some work.
LIKE
provides simple syntax , but not much functionality. According to another SO answer , the functionality is LIKE
quite specialized and therefore probably more efficient compared to the equivalent regex.
which is more applicable to use?
Not all of the text can be matched LIKE
, so in these cases you will have to use a regular expression. But if LIKE
enough, the linked answer suggests that it would be better to use this.
If you're worried about a specific request, use postgres EXPLAIN ANALYZE
to see which postgres will actually do.
source to share
Based on my research, POSIX Regular Expressions are more applicable than using the LIKE and ILIKE clauses because of some advantages:
- SPEED
- Request a simple offer
Here are some examples of using posix regex:
~ tilde for case sensitivity
POSIX: SELECT record FROM mytable WHERE record ~ 'a';
LIKEi: SELECT record FROM mytable WHERE record like '%a%';
~ * for case insensitivity
POSIX: SELECT record FROM mytable WHERE record ~* 'a';
LIKEi: SELECT record FROM mytable WHERE record ilike '%A%';
! ~ exclude / no (case sensitive)
POSIX: SELECT record FROM mytable WHERE record !~ 'a';
LIKEi: SELECT record FROM mytable WHERE record not like '%a%';
! ~ * exclude / none (case insensitive)
POSIX: SELECT record FROM mytable WHERE record !~ 'a';
LIKEi: SELECT record FROM mytable WHERE record not ilike '%a%';
Some uses of posix regex, LIKE and ILIKE can be found here .
source to share