How do I use COALESCE to return 'N/A' when the value of a TIMESTAMP column is NULL?

Question

Is it possible to use COALESCE (or any other way) to replace NULL values from a TIMESTAMP column with a string like 'N/A'?

In my SELECT statement I have a

CASE WHEN n.expiration_date::date IS NULL THEN 'N/A' ELSE n.expiration_date::date END

When I try this, I get this error, which makes sense:

invalid input syntax for type date: "N/A"

I found this blog post too about this problem. Is there a way around it?

If you use this in application, you might replace the `NULL`s there when showing the result. — sticky bit, May 09 '19 at 11:39
You could also just do it with COALESCE: `COALESCE(n.expiration_date::date::text, 'N/A')` — 404, May 09 '19 at 11:40

score 5 · Accepted Answer · answered May 09 '19 at 11:37

5

All values of a CASE expression have to evaluate to the same data type.

If you really need the N/A, you need to convert the date to a character type:

CASE 
    WHEN n.expiration_date IS NULL THEN 'N/A' 
    ELSE n.expiration_date::date::text 
END

Or if you want to have control over the format:

CASE 
    WHEN n.expiration_date IS NULL THEN 'N/A' 
    ELSE to_char(n.expiration_date, 'YYYY-MM-DD')
END

answered May 09 '19 at 11:37

That's what I did. Thanks! Related: https://stackoverflow.com/questions/43899262/using-coalesce-with-different-data-types – Vinayak May 09 '19 at 11:47

1 Answers1