Postgres perform aggregation on array or string_agg

Question

I have a column which is a string of comma seperated elements like this statuses= 'Initial,Initial,Completed,InProgress. And I what to make a status column like this:

CASE
    WHEN <all statuses are 'Initial'> THEN 'Initial'
    WHEN <all statuses are 'Completed'> THEN 'Completed'
    ELSE 'InProgress'
END AS status

I have tried bool_and(string_to_array(statuses,',')='Initial'), which does not compile. Any suguestions?

GMB · Accepted Answer · 2020-03-05 13:30:58Z

1

You could do this with string_to_array() and the ALL operator:

case 
    when 'Initial'   = ALL(string_to_array(statuses, ',')) then 'Initial'
    when 'Completed' = ALL(string_to_array(statuses, ',')) then 'Completed'
    else 'InProgress'
end status

Demo on DB Fiddle:

with t as (
    select 'Initial,Initial' statuses
    union all select 'Completed,Completed'
    union all select 'Initial,Completed,Other'
)
select 
    statuses,
    case 
        when 'Initial'   = ALL(string_to_array(statuses, ',')) then 'Initial'
        when 'Completed' = ALL(string_to_array(statuses, ',')) then 'Completed'
        else 'InProgress'
    end status
from t

statuses                | status    
:---------------------- | :---------
Initial,Initial         | Initial   
Completed,Completed     | Completed 
Initial,Completed,Other | InProgress

answered Mar 5, 2020 at 13:30

GMB

224k25 gold badges103 silver badges151 bronze badges

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

Collectives™ on Stack Overflow

Postgres perform aggregation on array or string_agg

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related