Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-novice by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group