To: "Victor Yegorov" <viy@pirmabanka.lv>
Cc: Rudi Starcevic <rudi@oasis.net.au>, pgsql-sql@postgresql.org
Subject: Re: CASE
References: <3E928853.7090801@oasis.net.au>
	<20030408094039.GS1712@pirmabanka.lv>
In-Reply-To: <20030408094039.GS1712@pirmabanka.lv>
From: Greg Stark <gsstark@mit.edu>
Organization: The Emacs Conspiracy; member since 1992
Date: 08 Apr 2003 08:06:15 -0400
Message-ID: <877ka519e0.fsf@stark.dyndns.tv>
Lines: 44
User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.2
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii


"Victor Yegorov" <viy@pirmabanka.lv> writes:

> ...COALESCE( MAX(e.edate),'2003-01-01')...
> 
> MAX(e.edate): will return date type (just a guess)
> '2003-01-01': will return char type (or varchar, doesn't matter).

No, 'xyz' in postgres isn't necessarily a string constant, it's an untyped
constant that the parser decides the type of based on context. In this case it
gets decided based on the other return values of the coalesce/case.

Notice the types chosen in this query:

db=# explain select * from t where t_start =  coalesce(now(),'2003-01-01') ;
                                                                                                         QUERY PLAN                                                                                                          
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on t  (cost=0.00..48223.40 rows=226 width=423)
   Filter: ((t_start)::timestamp with time zone = CASE WHEN (now() IS NOT NULL) THEN now() WHEN ('2003-01-01' IS NOT NULL) THEN '2003-01-01 00:00:00-05'::timestamp with time zone ELSE NULL::timestamp with time zone END)
(2 rows)


However, I suspect for some reason postgres is doing a string comparison in
your case. I don't understand why. Note that now() isn't a date, it's a
"timestamp with time zone", but I don't think that should be a problem. 
Try checking what types it uses in the plan for:

EXPLAIN SELECT * 
  FROM e 
 WHERE (CASE WHEN coalesce(max(e.edate),'2003-01-01') >= now()::date 
             THEN 'events' 
             ELSE 'noevents' 
              END
       ) = 'events';


Also, the standard date format should still sort correctly as alphanumeric, 
so I'm curious what you see when you do:

SELECT now();

--
greg