From: Victor Yegorov <viy@pirmabanka.lv>
To: Greg Stark <gsstark@mit.edu>
Cc: Rudi Starcevic <rudi@oasis.net.au>
Cc: pgsql-sql@postgresql.org
Subject: Re: CASE
Date: Tue, 8 Apr 2003 15:13:46 +0300
Message-ID: <20030408121346.GA13190@pirmabanka.lv> (raw)
In-Reply-To: <877ka519e0.fsf@stark.dyndns.tv>
References: <3E928853.7090801@oasis.net.au>
<20030408094039.GS1712@pirmabanka.lv>
<877ka519e0.fsf@stark.dyndns.tv>
* 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: viy@pirmabanka.lv, gsstark@mit.edu, rudi@oasis.net.au
Subject: Re: CASE
In-Reply-To: <20030408121346.GA13190@pirmabanka.lv>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox