Select date (timestamp) from PostgreSQL as string (char), beware of NULL value

Question

I want to select a date (my column is a timestamp type). But when in column is a NULL date, I want to return an empty string. How to do this? I wrote this:

SELECT
   CASE WHEN to_char(last_post, 'MM-DD-YYYY HH24:MI:SS') IS NULL THEN ''
      ELSE to_char(last_post, 'MM-DD-YYYY HH24:MI:SS') AS last_post END
   to_char(last_post, 'MM-DD-YYYY HH24:MI:SS') AS last_post, content
FROM topic;

But it shows me some errors, dont really know why:

ERROR:  syntax error at or near "as"
LINE 1: ...ELSE to_char(last_post, 'MM-DD-YYYY HH24:MI:SS') AS last_po...
                                                            ^

Solution 1

Using the COALESCE() function is the nicest approach, as it simply swaps in a substitute value in the case of a NULL. Readability is improved greatly too. :)

SELECT COALESCE(to_char(last_post, 'MM-DD-YYYY HH24:MI:SS'), '') AS last_post, content FROM topic;

Solution 2

You're putting your AS within the case?

Try:

SELECT
   CASE WHEN last_post IS NULL THEN ''
     ELSE to_char(last_post, 'MM-DD-YYYY HH24:MI:SS') END AS last_post,
   content
FROM topic;

I haven't tried the query though.

Solution 3

select coalesce(to_char(last_post, 'MM-DD-YYYY HH24:MI:SS'), '') as last_post, content
from topic;

Comments

[a_horse_with_no_name]

This can be simplified to case when last_post is null then '' else ...

[Jerry]

@a_horse_with_no_name You're right, thanks! I only focused on moving the AS ... later and didn't think about simplifying the query... I'll fix that right now!