Using COALESCE with different data types?

问题:

I have a query using COALESCE(timestamp_type::date,charachter_varying) which fails due to the mismatched data types:

ERROR: COALESCE types date and character varying cannot be matched

It works if i cast the timestamp as text:

COALESCE(timestamp_type::text,charachter_varying)

However, now it is returning the full timestamp when I only want YYYY-MM-DD (instead of the full timestamp, YYYY-MM-DD HH:MM:SS.000000+00)

How can I use COALESCE and return only the date portion of the timestamp?

回答1:

You can use to_char to convert the timestamp using appropriate format mask:

COALESCE(to_char(timestamp_type, 'YYYY-MM-DD'), varchar_col)

回答2:

The correct casting would be

COALESCE(timestamp_type::date::text,char_var)

This should work as you expect ... if you have the ISO datestyle. But it's MUCH better to not rely on datestyle settings for converting date-times to/from text. Hence, @Gurwinder Singh's answer is the way to go.