Sam Mason <sam@xxxxxxxxxxxxx> writes: > I'd interpret John's note as pointing out that SQL doesn't distinguish > between type declarations and type casting. I think he wants A.n to > be of type TEXT, would like to temporarily treat it as INTEGER in one > sub-expression. PG incorrectly propagates this cast as applying to the > whole query, leading to John getting his confusing error message. No, that's not correct at all. John had diagnosed the problem correctly: the planner was reordering the two separate WHERE conditions in such a way that the run-time cast to integer was attempted before the condition that would have eliminated the values for which the cast fails. (I believe that the reason is that for conditions that end up in the same plan node, the planner tries to push the more expensive ones to the end of the list; so his example with an extra trim() call works differently, having three operations to the other condition's two.) regards, tom lane
