ERROR: COALESCE types timestamp without time zone and integer cannot be matched (Postgresql)

Question

## PROBLEM ##

I got error from this script (Postgresql 9.3.2)
( It's OK in MS SQL Server )

SELECT 
 CASE COALESCE(my_date_field,0) 
 WHEN 0 THEN 0 
 ELSE 1 END 
 AS status
FROM 
 my_table

Error :COALESCE types timestamp without time zone and integer cannot be matched
Line 2 : CASE COALESCE(my_date_field,0)

## SOLVED ##

SELECT 
  CASE WHEN my_date_field IS NULL 
  THEN 0 ELSE 1 END 
  AS status
FROM 
  my_table

COALESCE accepts pretty much any number of parameters, but they should be the same data-type.
I quoted from COALESCE Function in TSQL

Answer 1

Zero is not a valid date. It's surprising that it works in MS SQL. You need to use a sensible date, or accept NULL.

 CASE COALESCE(my_date_field, DATE '0001-01-01') 

The query is a bit bizarre in general. Isn't that an incredibly longwinded and complicated way of writing IS NULL?

SELECT 
 my_date_field IS NULL AS status
FROM 
 my_table

If, per comment, you want 0 or 1, use:

SELECT 
 CASE WHEN my_date_field IS NULL THEN 1 ELSE 0 END AS status
FROM 
 my_table