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

Re: Querying now()

From: A Gilmore <agilmore(at)shaw(dot)ca>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: Querying now()
Date: 2004-12-20 02:34:45
Message-ID: 41C63A45.9070006@shaw.ca (view raw or flat)
Thread:
Lists: pgsql-novice
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

In response to

pgsql-novice by date

Next:From: Bruce BadgerDate: 2004-12-20 04:01:50
Subject: Drop does not always drop
Previous:From: Michael GlaesemannDate: 2004-12-20 00:57:22
Subject: Re: Querying now()

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