From: | "Victor Yegorov" <viy(at)pirmabanka(dot)lv> |
---|---|
To: | Greg Stark <gsstark(at)mit(dot)edu> |
Cc: | Rudi Starcevic <rudi(at)oasis(dot)net(dot)au>, pgsql-sql(at)postgresql(dot)org |
Subject: | Re: CASE |
Date: | 2003-04-08 12:13:46 |
Message-ID: | 20030408121346.GA13190@pirmabanka.lv |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
* Greg Stark <gsstark(at)mit(dot)edu> [08.04.2003 15:07]:
>
> "Victor Yegorov" <viy(at)pirmabanka(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2003-04-08 14:30:37 | Re: CASE |
Previous Message | Greg Stark | 2003-04-08 12:06:15 | Re: CASE |