From: Greg Stark <gsstark@mit.edu>
To: Victor Yegorov <viy@pirmabanka.lv>
Cc: Rudi Starcevic <rudi@oasis.net.au>
Cc: pgsql-sql@postgresql.org
Subject: Re: CASE
Date: 08 Apr 2003 08:06:15 -0400
Message-ID: <877ka519e0.fsf@stark.dyndns.tv> (raw)
In-Reply-To: <20030408094039.GS1712@pirmabanka.lv>
References: <3E928853.7090801@oasis.net.au>
<20030408094039.GS1712@pirmabanka.lv>
"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
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: gsstark@mit.edu, viy@pirmabanka.lv, rudi@oasis.net.au
Subject: Re: CASE
In-Reply-To: <877ka519e0.fsf@stark.dyndns.tv>
* 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