Friday, March 24, 2017
Ever wonder why using select column aliases in where clauses fails?
SELECT random() AS confusion FROM generate_series(1,10) WHERE confusion > 0.5; ERROR: column "confusion" does not exist LINE 3: WHERE confusion > 0.5;
It is because the order in which select clauses are evaluated is specified by the sql standard, and it isn't top to bottom. Tom Lane's excellent email post goes into the details. The thread also mentions the unfortunate effect that users of union often want the behavior of union all.