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

Nerdwood · Accepted Answer · 2013-04-13 16:56:57Z

33

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;

answered Apr 13, 2013 at 16:56

Nerdwood

4,0821 gold badge25 silver badges20 bronze badges

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

Comments

Clodoaldo Neto · Accepted Answer · 2013-04-13 16:57:10Z

3

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

answered Apr 13, 2013 at 16:57

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Comments

Jerry · Accepted Answer · 2013-04-13 18:16:16Z

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, 2013 at 18:16

answered Apr 13, 2013 at 16:56

Jerry

71.8k14 gold badges106 silver badges148 bronze badges

2 Comments

user330315 Over a year ago

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

Jerry Over a year ago

@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!

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related