Column comparison manipulation?

[Ridiculon]

I am involved in a project that is moving databases from sybase to postgres, the main issue with this is that sybase 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 script 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 (comparing to a list of columns that should be case insensitive), 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')

Mirror to StackOverflow question

[georgesittas]

You can search for the specific AST nodes you mentioned and conditionally replace them with the case-insensitive operators of interest. You can also apply a transformation to the whole AST to automatically do this for every target node.

See:

• https://github.com/tobymao/sqlglot#metadata
• https://github.com/tobymao/sqlglot#build-and-modify-sql
• https://github.com/tobymao/sqlglot/blob/main/sqlglot/expressions.py#L554