Peter Eisentraut <peter_e@gmx.net> writes:
> Jeff Dwyer wrote:
>> This seems like a bug to me. Why should an explicit cast be necessary after
>> a coalesce?
> Because coalesce(null, '1900-1-2') has no other type information attached, so
> it would have picked text by default as result type, and that then clashes
> with the result type of coalesce(null,current_date), which can be derived to
> be date. This is a robustness improvement: 8.2 and earlier would silently
> accept coalesce(null, 'abc') and apply text-semantics comparison.
Yes. The query "worked" in pre-8.3 only for rather small values of
"work": if you had been using a non-ISO datestyle the comparisons would
in fact have come out wrong. Also, it being a textual rather than date
comparison, any index on the date column being compared to wouldn't have
been used.
regards, tom lane