Changing a weird Mysql query to Postgresql that uses group_concat

Question

I'm moving our backend database from mysql to postgres and am in the process of migrating all of our old queries / functions. Most of them are trivial to do, but today I ran across one that has me scratching my head. Here it is:

UPDATE
  k_coderound AS cr, k_coderound AS cr_m
SET
  cr.is_correct = IF(
    (SELECT GROUP_CONCAT(option_id ORDER BY variable_id) AS `values` FROM k_value    
         WHERE code_round_id=cr.id GROUP BY code_round_id) = 
    (SELECT GROUP_CONCAT(option_id ORDER BY variable_id) AS `values` FROM k_value 
         WHERE code_round_id=cr_m.id GROUP BY code_round_id),
    1,
    0
  )
WHERE     
 cr.is_master=0 
 AND cr_m.is_master=1 
 AND cr_m.object_id=cr.object_id 
 AND cr_m.content_type_id =cr.content_type_id       

I know that Postgres has no group_concat and one should instead use array_agg. My problem is that I can't figure out what is going on exactly- this query was written ages ago by someone who isn't with us anymore. Also compounding this difficult is the lack of the IF statement in Postgres. If anyone is able to provide feedback or advice I'd greatly appreciate it!

Answer 1

It's hard to tell what the intention is. My approach to this would be to first find a SELECT statement that returns the "target" data.

Something like this:

select cr.is_correct,
       array_agg(case when kv1.code_round_id = cr.id then kv1.option_id else null end, ',' order by kv1.variable_id) as kv_values1,
       array_agg(case when kv2.code_round_id = cr_m.id then kv2.option_id else null end, ',' order by kv2.variable_id) as kv_values2
from k_coderound cr
  join k_value kv1 on kv1.code_round_id = cr.id
  join k_coderound cr_m 
       on cr_m.object_id=cr.object_id 
      and cr_m.content_type_id =cr.content_type_id 
  join k_value kv2 on kv2.code_round_id = cr_m.id
where cr.is_master=0 
  and cr_m.is_master=1 

This is most probably not correct, but I think it shows how the non-standard MySQL expressions could be translated to standard SQL (and thus to PostgreSQL)

Once this seems to do the right thing, I'd wrap that into the UPDATE statement:

update k_coderound cru
  set cr.is_correct = case 
                        when t.kv_values1 = t.kv_values2 then 1 
                        else 0 
                      end
from (select cr.ctid, 
             array_agg(case when kv1.code_round_id = cr.id then kv1.option_id else null end, ',' order by kv1.variable_id) as kv_values1,
             array_agg(case when kv2.code_round_id = cr_m.id then kv2.option_id else null end, ',' order by kv2.variable_id) as kv_values2
    from k_coderound cr
      join k_value kv1 on kv1.code_round_id = cr.id
      join k_coderound cr_m 
           on cr_m.object_id=cr.object_id 
          and cr_m.content_type_id =cr.content_type_id 
      join k_value kv2 on kv2.code_round_id = cr_m.id
    where cr.is_master=0 
      and cr_m.is_master=1 
) t
where t.ctid = cru.ctid

I'm pretty sure I have missed some syntax things, but hopefully this gets you started.