Search code examples
sqlpostgresqltype-conversioncoalesce

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?

Solution

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

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