Re: CASE

* Greg Stark <gsstark@mit.edu> [08.04.2003 15:07]:
>
> "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.

Yes, yes. I know that. This is what I ment by saing `I'm not sure' in the
first reply.

>
> 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:

To avoid string comparison I've suggested to cast into date format
manually.

Actually, I have never came across with such situation, so I don't know the
exact solution.

--

Victor Yegorov