SQL tuple/lexicographic comparison with multiple directions

Question

I need to return elements from a database query based on an inequality using the lexicographic ordering on multiple columns. As described in this question this is straightforward if I'm comparing all the columns in the same direction. Is there a straigtforward way to do this if I want to reverse the direction of the sort on some columns.

For instance, I might have columns A, B and C and values 5, 7, and 23 and I'd like to return something like:

WHERE A < 5 OR (A = 5 AND B > 7) OR (A = 5 AND B = 7 AND C < 23)

Is there any easier way to do this using tuples (I have to construct in a function without knowing the number of columns beforehand)? Note that, some columns are DateTime columns so I can't rely on tricks that apply only to integers (e.g. negating both sides). I'm happy to use postgresql specific tricks.

And, if not, is there a specific way/order I should build expressions like the above to best use multicolumn indexes?

Answer 1

Referring to the thread you mentioned, can you try the idea WHERE (col_a, 'value_b') > ('value_a', col_b)