I am trying to do an inexact join (I am not sure what the proper term is) where I could perform pattern matching. Basically, instead of doing a JOIN like this:
.... JOIN .... ON (t1.col = t2.col)
I would like to do do something like:
.... JOIN .... ON (t1.col ILIKE %(t2.col)% )
The second example is obviously not proper syntax. Is there a way to do something like that?
.... JOIN .... ON t1.col ILIKE '%' || t2.col || '%'
Please note that as written, AFAIK, PostgreSQL won't be able to use any indexes to speed up the join processing.
An alternative way to join on “is the value of t2.col a substring of t1.col”:
... AS t1 JOIN ... AS t2 ON POSITION(t2.col IN t1.col)<>0
This still can't use indexes, but the advantage is you don't have to worry about % and _ characters in t2.col which would otherwise start matching everything.
If you need case-insensitive matching like ILIKE and you're not using citext you'd need to LOWER() both columns before using POSITION().