I'm trying to match a certain text that includes a single quote (i.e. 'company's report...') normally I would have used the E' literal + ' or double single quotes. but when it gets to using the LIKE '%' operator, things got complicated. what is the best approach to match a text with a single quote?
You can use Dollar-quoted String Constants at Lexical Structure
Your condition should be something like below;
select * from atable
where afield like $$Dianne's %$$
You can escape single quote with another single quote. Example:
WHERE column LIKE 'RSNboim''s'
From https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS
To include a single-quote character within a string constant, write two adjacent single quotes, e.g.,
'Dianne''s horse'. Note that this is not the same as a double-quote character (").