Re: Querying now()

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: A Gilmore <agilmore(at)shaw(dot)ca>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Querying now()
Date: 2004-12-20 00:46:56
Message-ID: 8211.1103503616@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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

In response to

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Michael Glaesemann 2004-12-20 00:57:22 Re: Querying now()
Previous Message M. Bastin 2004-12-20 00:44:38 Re: Front end?