Laurenz Albe <[email protected]> writes:
> On Mon, 2026-07-27 at 13:06 -0400, Tom Lane wrote:
>> It would behave very strangely I think: you'd have a situation where,
>> eg,
>> A::timestamp > B::timestamptz
>> B::timestamptz < A::timestamp
>> sometimes give different answers, so that we'd have to stop marking
>> them as commutators. The downsides of that seem pretty awful.
> I thought about that for a while, but I can't see how it could happen.
> If the "timestamptz" side is cast to "timestamp", it would be cast to
> the same value in both cases. So that would mean that there are two
> "timestamp" values where x > y is different from y < x.
AFAICS, what we'd need to pursue this route is that:
1. timestamptz_cmp_timestamp, which is used for an index on
timestamptz with a query value of type timestamp, would have to
promote the timestamp to timestamptz and then compare (same as
it ever was). If you instead convert the timestamptz side then
you have the exact same problem that's being complained of,
though probably for different data values: the index sort
ordering looks inconsistent.
2. timestamp_cmp_timestamptz, which is used for an index on
timestamp with a query value of type timestamptz, would have to
convert the timestamptz side to timestamp and then compare.
If we sync the SQL operators with that, then what we have is a
situation where timestamptz-on-the-left cases are inconsistent
with timestamptz-on-the-right cases. I don't see that people
will find that intuitive.
regards, tom lane
