1

I need to display blank when I have NULL in timestamp field, my code looks like this: 

SELECT 
CASE WHEN exp_date IS NULL THEN CAST(' ' AS TIMESTAMP)
ELSE exp_date END

But it throws an error. I don't want to display NULL. Any suggestion is appreciated.

Improve this question

2 Answers 2

Reset to default
3 
SELECT COALESCE(exp_date::TEXT,'')
FROM ...

The COALESCE function will return the first value that is non-null, which in this case is a blank.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

This won't work as exp_date is a timestamp, you can't COALESCE a timestamp and text.
Right you are. (worked in my local test db, typo'd the cast in the answer). Thanks for pointing that out.
WOW , I like the code; it works on my local DB as well but I used it in another query
COALESCE is generally going to be faster than CASE statements, but if you aren't doing dozens to thousands of queries per second it might not be noticeable in your environment.
2

You can't cast whitespace to a timestamp. "Display" is really up to the application displaying the data. I use PGAdmin 3, and by default that shows NULLs as "blank", though you can configure it to show something else like <NULL> for NULLs instead.

The best solution is update your code to display a blank or whatever you want when the field is NULL. Otherwise you could cast the timestamp to TEXT so you can display whatever you want from the PG side:

SELECT CASE WHEN exp_date IS NULL THEN ' ' ELSE exp_date::TEXT END

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.