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!

Can you provide to which table the option_id , variable_id and code_round_id columns belong? Do they belong to k_value? — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Jun 12, 2012 at 15:46
IF usually translates to a CASE in PostgreSQL (and any other standard compliant SQL database) — user330315
– user330315, Commented Jun 12, 2012 at 16:26

user330315 · Accepted Answer · 2012-06-12 16:46:09Z

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.

Collectives™ on Stack Overflow

Changing a weird Mysql query to Postgresql that uses group_concat

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related