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

score 29 · Accepted Answer · answered Apr 13 '13 at 16:56

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;

Jerry · Answer 2 · 2013-04-13T18:16:16.900

3

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.

edited Apr 13 '13 at 18:16

answered Apr 13 '13 at 16:56

Jerry

70,495
13
100
144

1

This can be simplified to `case when last_post is null then '' else ...` – Apr 13 '13 at 17:01
@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! – Jerry Apr 13 '13 at 17:03

score 3 · Answer 3 · answered Apr 13 '13 at 16:57

3

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

answered Apr 13 '13 at 16:57

Clodoaldo Neto

118,695
26
233
260

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

3 Answers3