PostgreSQL - How to replace empty field with any string?

Question

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.

Does your query work without the replace empty field part? — Álvaro González
– Álvaro González, Commented Dec 17, 2013 at 9:35
Yes, it works. Only thing I want to achieve is to replace those empty dates with any string I want. — Boba_Fett
– Boba_Fett, Commented Dec 17, 2013 at 9:40

marvinorez · Accepted Answer · 2013-12-17 12:58:14Z

1

You can simply cast timestamp to text using ::text

select column1, coalesce(date_trunc('seconds', min(date))::text, 'any_string') as column2

answered Dec 17, 2013 at 12:58

marvinorez

6035 silver badges7 bronze badges

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

2 Comments

Boba_Fett Over a year ago

I've tried that, but still it returns empty fields instead of text :(

marvinorez Over a year ago

select q1.id, coalesce(q2.date::text, 'any_string'), coalesce(q3.date2::text, 'any_string') from ...

Álvaro González · Accepted Answer · 2013-12-17 09:47:16Z

1

The date_trunc() function returns a timestamp, thus you cannot fit a string like any_string in the same column.

You'll have to pick a format and convert the resulting date to string, though of course it'll no longer be usable as date.

answered Dec 17, 2013 at 9:47

Álvaro González

147k45 gold badges282 silver badges378 bronze badges

2 Comments

Boba_Fett Over a year ago

I've converted date_trunc() using to_char() function, but now it's giving me empty fields like before, without error: coalesce(to_char(date_trunc('seconds', min(date)), 'YYYY-MM-DD HH24:MI:SS'), 'any_string')

Álvaro González Over a year ago

I cannot reproduce it. You should post a full self-complete example, including table creation, sample data and actual query.

captain_a · Accepted Answer · 2013-12-17 10:12:31Z

-1

the coalesce function only works on the integer data type. It will not work on any other data type .

In one of cases I used to convert a varchar data type to integer inside the coalesce function by using _columnName_ :: integer syntax . But in your case i dont think so that time stamp will be converted to the integer data type.

answered Dec 17, 2013 at 10:12

captain_a

3,2871 gold badge16 silver badges24 bronze badges

Collectives™ on Stack Overflow

PostgreSQL - How to replace empty field with any string?

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related