Thread: Possible Typecasting Bug with coalesce()

Hello everyone, I found something that struck me as odd revolving
around automatic typecasting and coalesce. It appears as though a
timestamp will not automatically be cast to a timestamp if the
timestamp is coalesced. Consider the following example:
=> select now()>'Jul 14 2006 9:16:47AM';?column?
----------t
(1 row)

=> select now()>coalesce('Jul 14 2006 9:16:47AM');?column?
----------f
(1 row)

=> select now()>coalesce('Jul 14 2006 9:16:47AM')::timestamp with time
zone;?column?
----------t
(1 row)

=> select now()>coalesce('Jul 14 2006 9:16:47AM'::timestamp with time
zone);?column?
----------t
(1 row)

Just wanted to know if anyone was aware of this behavior and if it is
correct. 

Thanks, 
Scott.

"MotherMGA" <sbbowers@gmail.com> writes:
> => select now()>coalesce('Jul 14 2006 9:16:47AM');

The coalesce() function is going to resolve its datatype as "text" in
this situation, and then text dominates timestamp in the comparison
(ie, the result of now() is coerced to text).  When you write

>  => select now()>'Jul 14 2006 9:16:47AM';

the literal's type doesn't have to be resolved until it's compared to
now(), and that comparison is what gives the parser the hint that the
literal ought to be considered to be a timestamp rather than just text.

> => select now()>coalesce('Jul 14 2006 9:16:47AM'::timestamp with time
> zone);

This is what you need to do if you want the literal to be treated as
timestamp right off.

The only bug I see here is that implicit coercions to text are a bad
idea :-( --- IMHO it would be better if your first query failed instead
of giving you unexpected behavior.
        regards, tom lane

> > => select now()>coalesce('Jul 14 2006 9:16:47AM');

> The only bug I see here is that implicit coercions to text
> are a bad idea :-( --- IMHO it would be better if your first
> query failed instead of giving you unexpected behavior.

:-) We know that you think that Tom, but a lot of us do not
want to go casting all our sql, especially where other db's don't
require it.

Would an equivalent CASE statement also do the early conversion to text
?

Andreas

You are correct, Andreas.

=> select now()>'Jul 14 2006 9:16:47AM';?column?
----------t
(1 row)

=> select now() > CASE WHEN 'Jul 14 2006 9:16:47AM' IS NOT NULL THEN
'Jul 14 2006 9:16:47AM' END;?column?
----------f
(1 row)

I've also found that there must be more than one typecasting function
being used because the result can be different depending on the format
of the timestamp string:

=> select now()>coalesce('Jul 14 2006 9:16:47AM');?column?
----------f
(1 row)

=> select now()>coalesce('2006-07-14 9:16:47');?column?
----------t
(1 row)