Andrew Gierth <and...@tao11.riddles.org.uk> writes: > "Tom" == Tom Lane <t...@sss.pgh.pa.us> writes: > Tom> Lastly, your proposed use-case has some attraction, but this > Tom> proposal only supports it if the column you need to be differently > Tom> sorted is textual. What if the sort columns are all numerics and > Tom> timestamps?
> There are already trivial ways to reverse the orders of those, viz. > (-number) and (-extract(epoch from timestampcol)). The lack of any > equivalent method for text is what prompted this idea. Those "trivial ways" have failure cases, eg with INT_MIN. I don't buy that this argument justifies introducing a kluge into text comparison. > Tom> Thinking about that, it seems like what we'd want is some sort of > Tom> more-general notion of row comparison, to express "bounded below > Tom> in an arbitrary ORDER BY ordering". Not quite sure what it ought > Tom> to look like. > Well, one obvious completely general method is to teach the planner > (somehow) to spot conditions of the form > (a > $1 OR (a = $1 AND b > $2) OR (a = $1 AND b = $2 AND c > $3) ...) > etc. and make them indexable if the sense of the > or < operator at > each step matched an ASC or DESC column in the index. I think really the only attraction of that is that it could be argued to be standard --- but I rather doubt that it's common for DBMSes to recognize such things. It'd certainly be a royal pain in the rear both to implement and to use, at least for more than about two sort columns. Back at https://www.postgresql.org/message-id/10492.1531515255%40sss.pgh.pa.us I proposed that we might consider allowing row comparisons to specify an explicit list of operators: >> One idea for resolving that is to extend the OPERATOR syntax to allow >> multiple operator names for row comparisons, along the lines of >> ROW(a,b) OPERATOR(pg_catalog.<, public.<) ROW(c,d) I wonder whether it'd be feasible to solve this problem by doing that and then allowing the operators to be of different comparison types, that is "ROW(a,b) OPERATOR(<, >) ROW(c,d)". The semantics would be that the first not-equal column pair determines the result according to the relevant operator. But I'm not quite sure what to do if the rows are in fact equal --- if some of the operators are like "<" and some are like "<=", what should the result be? Maybe let the last column's operator decide that? regards, tom lane
