Re: order of clauses

Patrick Welche <prlw1(at)newn(dot)cam(dot)ac(dot)uk> writes:
> select x/y from vals where y>0 and x/y>1;

> will give a divide by zero error as A=(y>0) and B=(x/y>1) can be evaluated in
> any order (A and B = B and A). I obviously would like (y>0) to happen first,
> but I don't see how this can be achieved.. Any ideas?

Of course you can rewrite this particular case to avoid the division,
but I suppose you are looking for a more general answer.
Consider something like

CASE WHEN y > 0 THEN x/y > 1 ELSE false END

I think that right now, the planner gratuitously reverses the order of
the WHERE clauses that it's unable to convert to index/join quals, thus
your failure. So you could hack around the problem just by switching
the two conditions. I've been meaning to try to figure out where the
reversal is happening and undo it, however, so this behavior should not
be considered to be documented/supported/guaranteed.

regards, tom lane