Re: Querying now()

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