Hello all,
how to build an multicolumn index with one column order ASCENDING and
another column order DESCENDING?
The use case that I have is that I use 2 column index where the first
column is kind of flag and the second column is an actual ordering
column. The flag should be always ordered DESCENDING, but the second
column is ordered DESCENDING when it is a numeric column, and
ASCENDING when it is a text column.
CREATE TABLE storage (id int, flag int, numeric_data int, text_data
text);
SELECT * FROM storage
ORDER BY flag DESC, numeric_column DESC
LIMIT 20 OFFSET 0;
SELECT * FROM storage
ORDER BY flag DESC, text_column ASC
LIMIT 20 OFFSET 0;
Definitely the multicolumn index on (flag, numeric_column) is being
used.
But how to create an index on (flag, text_column DESC)?
I will try to index by ((-flag), text_column) and sort by (-flag) ASC,
but it, to say the truth, does not really look like a nice solution.
