How do I replace blanks rows in text array using CASE statment in PostgreSQL?

Question

I have 12 unique IDs in my PostgreSQL 9.5 table for which some array (text) are blanks:

ID(int)     my_array(text)
1           1,112,298
2
3           2,114,235,145,126,123,141
..          ..

I am trying to replace these blank rows with '0' in my query but so far failed to do so:

Select
      Case when my_tbl.my_array = ' '
      then '0'
      else my_array
      end as array
from my_table

The query runs but no result. Can somebody help to me to replace blanks rows using case statement or otherwise?

use my_tbl.my_array = '{}' in case to check for empty arrays. — Vamsi Prabhala
– Vamsi Prabhala, Commented Jun 15, 2017 at 12:34
I used, CASE when my_table.my_array = '{}' then '{0}'... but didn't work. — khajlk
– khajlk, Commented Jun 15, 2017 at 12:38

klin · Accepted Answer · 2017-06-15 12:37:44Z

2

select id, coalesce(my_array, '0') as my_array
from my_table;

answered Jun 15, 2017 at 12:37

klin

123k15 gold badges241 silver badges263 bronze badges

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

1 Answer 1