Re: Querying now()

Tom Lane wrote:
> A Gilmore <agilmore@shaw.ca> writes:
>
>>Id like my application to do something like this :
>
>
>>SELECT now() AS currentTime WHERE currentTime < '$timestamp';
>
>
>>So if it returns a row Id know $timestamp is not in the past.  However
>>when you run this it simply says 'ERROR:  column "currentTime" does not
>>exist'.
>
>
> Well, you could say
>
>   SELECT 1 WHERE now() < '$timestamp';
>
> and see if you get a row back or not; or even more directly,
>
>   SELECT now() < '$timestamp';
>
> and see whether you get 't' or 'f' back.
>
> I think a lot of novices expect something like the above to work because
> they are confused about the evaluation order of SQL clauses.  The
> logical model for SQL SELECT commands is:
>
>     1.  Compute table described by FROM clause.
>     2.  Filter out rows that don't pass the WHERE clause.
>     3.  If grouping or aggregating, compute row groups.
>     4.  Filter out row groups that don't pass the HAVING clause.
>     5.  Compute the SELECT output columns.
>     6.  Order the result rows according to ORDER BY.
>
> Thus, the only auxiliary clause in which it makes sense to refer to
> output columns is ORDER BY.  Before that, the output row doesn't even
> exist.  An example of why you'd not like this rule to be ignored is
>
>     SELECT 1/x AS y FROM tab WHERE x != 0;
>
> If you could refer to y in the WHERE clause then this would fail to
> achieve the desired result of avoiding a divide-by-zero error.
>
>             regards, tom lane
>

That works great.  Thank you that information is very helpful.

A Gilmore