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

Craig Ringer · Accepted Answer · 2014-02-19T13:14:20.983

13

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

edited Feb 19 '14 at 13:14

answered Feb 19 '14 at 09:33

Craig Ringer

I just need to store 0 (if my_data_field is null) and 1 (if my_data_field is not null) in the new field 'status' – postgreat Feb 19 '14 at 11:36

1 Answers1