I have a table called diary. I insert several messages into it almost every day.

I sometimes, for example, call Microsoft "Microjunk", "Micro$oft", "M$" and many other names. If I later search for "Microsoft", it won't find the posts where I called them "Micro$oft", etc.:

SELECT * FROM diary WHERE message ILIKE '%Microsoft%';

So I need to construct some kind of "custom synonyms" table, so that any search for "Microsoft" also finds diary entries containing "M$", etc. If I simply make it a table with two columns, which seems like the most logical approach to me, how can I integrate that into my ILIKE search?

"source word",  "synonym"
'Microsoft',    'Microjunk',
'Microsoft',    'Micro$oft',
'Microsoft',    'M$',
'McDonald\'s',  'EvilBurger',
'McDonald\'s',  'McEvil',

If possible, please don't refer me to the "fulltext search" documentation, because I've loaded that part of the manual 100+ times over the last 15 years and never came back understanding one bit about it! Something about it just seems impossible for my brain to grasp, even though I want to understand and be able to use it.

  • @AMtwo I forgot that this is a general database category... Jan 9, 2022 at 20:30
  • But....what if full text search is the right answer? What if you could explain what about you don't get, so we can help you? (In a different question)
    – jjanes
    Jan 10, 2022 at 1:22

1 Answer 1


You would want a join.

SELECT * FROM diary JOIN synonym ON diary.message ILIKE synonym.synonym where synonym.canonical_word ='Microsoft';

Here I assume the "%" symbols are part of the text in the synonym.synonym column. You could dynamically add then within the ON itself if you wanted to do it that way.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.