ERROR: CASE types text and text[] cannot be matched in postgresql

Viewed 15

I am writing case statement in a materialized view as below

CASE 
            WHEN ((length(comp_serv_view.services_rolled_id) > 0) AND (length(comp_serv_view.company_service_id) > 0)) THEN string_to_array(concat(comp_serv_view.services_rolled_id, ',', comp_serv_view.company_service_id),','::text)
            WHEN ((length(comp_serv_view.services_rolled_id) > 0) AND ((length(comp_serv_view.company_service_id) = 0) OR (comp_serv_view.company_service_id IS NULL))) THEN string_to_array(comp_serv_view.services_rolled_id,','::text)
            WHEN (((length(comp_serv_view.services_rolled_id) = 0) OR (comp_serv_view.services_rolled_id IS NULL)) AND (length(comp_serv_view.company_service_id) > 0)) THEN string_to_array(comp_serv_view.company_service_id,','::text)
            ELSE ''::text
        END AS top_3_company_service_id,

However i am getting below error

ERROR:  CASE types text and text[] cannot be matched
LINE 22: ...gth(comp_serv_view.company_service_id) > 0)) THEN string_to_...
                                                              ^
SQL state: 42804
Character: 1964

can someone help me to understand what has gone wrong here? I have converted each case to text but getting the error also will be helpful if you can give example of how to use string to array with a concat statement.

0 Answers
Related