Re: Possible Typecasting Bug with coalesce()

"MotherMGA" <sbbowers(at)gmail(dot)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