I am involved in a project that is moving databases from Sybase SQL Anywhere to postgres, the main issue with this is that Sybase SQL Anywhere is natively case insensitive and postgres is not.
The patch for this (at the moment) is to use case insensitive comparisons (ILIKE) in the queries embedded in our application (I know that there are issues and tradeoffs with this but it is not my decision - no point discussing it here unless you have some killer alternative).
I'm attempting to put together a python script utilizing SQLGlot to automate this as much as I can - so the question is:
How can I isolate column comparisons (= , <> , like, not like) including the column names on both sides - I need to find them and evaluate the columns, then change the comparison operator as appropriate and inject it all back into the original query.
Example:
SELECT a.case_insensitive_col,
CASE
WHEN b.case_insensitive_col = 'someText'
THEN b.someColumn
ELSE b.someOtherColumn
END as conditional_selection,
b.reg_column
FROM tableA a
JOIN tableB b ON a.case_insensitive_col = b.case_insensitive_col
WHERE a.case_insensitive_col = ?
AND b.reg_column = ?
---->
SELECT a.case_insensitive_col,
CASE
WHEN b.case_insensitive_col ILIKE 'someText'
THEN b.someColumn
ELSE b.someOtherColumn
END as conditional_selection,
b.reg_column
FROM tableA a
JOIN tableB b ON a.case_insensitive_col ILIKE b.case_insensitive_col
WHERE a.case_insensitive_col ILIKE ?
AND b.reg_column = ?
There are 3 examples (comparison in case in select, comparison in join, comparison in where) of the cases I've seen so far, is this possible?
I also want to avoid affecting the '=' characters in update statements eg(UPDATE tableA SET columnA = 'sometext')
