Re: Querying now()

Tom Lane wrote:
> A Gilmore <agilmore(at)shaw(dot)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