Array Comparison in PostgreSQL

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, m.type from 
    (select id_mot1 AS id, array_sort(array_agg(annoying_types)) AS input_type, 
        (select m.* from import_temp as m order by m.id_input, 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.


0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published.