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

Katie picture Katie · Apr 13, 2013 · Viewed 41.9k times · Source

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...
                                                            ^

Answer

Nerdwood picture Nerdwood · Apr 13, 2013

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;