I'm trying to replace some empty (NULL) fields, which I get as a result of my query, with any string I want. Those empty fields are placed in a "timestamp without timezone" column. So I tried to use COALESCE function, but no result (I got error: invalid input syntax for timestamp: "any_string":
select column1, coalesce(date_trunc('seconds', min(date)), 'any_string') as column2
What could be wrong?
Table:
╔════╦═════════════════════╦═════════════════════╗
║ id ║ date ║ date2 ║
╠════╬═════════════════════╬═════════════════════╣
║ 1 ║ 2013-12-17 13:54:59 ║ 2013-12-17 09:03:31 ║
║ 2 ║ 2013-12-17 13:55:07 ║ 2013-12-17 09:59:11 ║
║ 3 ║ 2013-12-17 13:55:56 ║ empty field ║
║ 4 ║ 2013-12-17 13:38:37 ║ 2013-12-17 09:14:01 ║
║ 5 ║ 2013-12-17 13:54:46 ║ empty field ║
║ 6 ║ 2013-12-17 13:54:46 ║ empty field ║
║ 7 ║ 2013-12-17 13:55:40 ║ empty field ║
╚════╩═════════════════════╩═════════════════════╝
Sample query:
select q1.id, q2.date, q3.date2
from (select distinct id from table1) q1
left join (select id, date_trunc('seconds', max(time)) as date from table2 where time::date = now()::date group by id) q2 on q1.id = q2.id
left join (select id, date_trunc('seconds', min(time2)) as date2 from table1 where time2:date = now()::date group by id) q3 on q1.id = q3.id
order by 1
And the matter is to replace those empty field above with any string I imagine.
You can simply cast timestamp to text using ::text
select column1, coalesce(date_trunc('seconds', min(date))::text, 'any_string') as column2