Insert values to new array column based on conditions in PostgreSQL

Question

What i have

  id  test_1   test_2  test_3        Indicator_column
  1     651       40     0.4      {test_1,test_2,test_3}
  1     625       80     0.6      {test_1,test_2,test_3}
  1     510       60     0.78     {test_1,test_2,test_3}
  1     710       90     0.4      {test_1,test_2,test_3}
  1     550       Null   0.2      {test_1,test_2,test_3}

What i need:

I have these conditions in table and also in excel

1) if test 1 has values between 650-800 then 1 else 0

2) if test 2 has value greater than 80 then 1 else 0

3) if test 3 has values greater than 0.5 then 1 else 0

    id  test_1   test_2  test_3        Indicator_column        exclude_flag
     1     651       40     0.4      {test_1,test_2,test_3}      {1,0,0}
     1     625       80    0.6       {test_1,test_2,test_3}      {0,0,1}
     1     510       60     0.78     {test_1,test_2,test_3}      {0,0,1}
     1     710       90     0.4      {test_1,test_2,test_3}      {1,1,0}
     1     550       Null   0.2      {test_1,test_2,test_3}      {0,0,0}

Answer 1

You can construct an ARRAY[] with elements based on CASE conditions like below:

select
  id, test_1, test_2, test_3, indicator_column,
  ARRAY[
    case when test_1 between 650 and 800 then 1 else 0 end,
    case when test_2 > 80 then 1 else 0 end,
    case when test_3 > 0.5 then 1 else 0 end,
  ] as exclude_flag
from t