I just had an input table, where I wanted to map multiple items to a single code.
I had foo and bar which I wanted to map to baz. They could be in any order though, (bar and foo) so I couldn’t just aggregate a string and compare those.
Instead I used array_agg and array_sort
select i.id, m.type from (select id_mot1 AS id, array_sort(array_agg(annoying_types)) AS input_type, from (select m.* from import_temp as m order by m.id_input, m.id asc) AS t group by id_input) as i, type_map as m where i.input_type = array_sort(m.input_type) ;
I create an array of the grouped values using array_agg, sort it using array_sort and then compare it to the sorted mapping. Sorting on insert into the mapping would make more sense though.
array_sort is from the post by David Fetter at PostgreSQL SQL Tricks.