How should I construct a "custom synonyms" table?

Question

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.

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.