Migrate data of the uuid [] columns to another table and remove duplicates from the array when trans-CodePudding

there is a table1:

table2_id   bigint fkey
column_one  UUID[] 
column_two  UUID[] 
another columns...

table2_id |                               column_one                                   |
---------- ---------------------------------------------------------------------------- 
 1        | {451a9ab7-02f6-4f63-bb87-80ad531ab490,f3161729-181a-4afb-a44e-6d441b04e4f5}
 1        | {451a9ab7-02f6-4f63-bb87-80ad531ab490}
 1        | {451a9ab7-02f6-4f63-bb87-80ad531ab490,ac8ffa40-5ac0-484b-8526-04fd577c4bf6}
 2        | {451a9ab7-02f6-4f63-bb87-80ad531ab490,ca73d560-2508-408c-bd63-d7a4e51de149}
 2        | {ca73d560-2508-408c-bd63-d7a4e51de149,f3161729-181a-4afb-a44e-6d441b04e4f5}

column_two is the same.

The data from these columns must be migrated to the same columns in Table2 (I created similar columns - column_one, column_two), removing duplicates from the data array.

This is how the data in the table2 should look:

id |                                                  column_one                                                     |
--- ----------------------------------------------------------------------------------------------------------------- 
1  | {451a9ab7-02f6-4f63-bb87-80ad531ab490,f3161729-181a-4afb-a44e-6d441b04e4f5,ac8ffa40-5ac0-484b-8526-04fd577c4bf6}
2  | {451a9ab7-02f6-4f63-bb87-80ad531ab490,ca73d560-2508-408c-bd63-d7a4e51de149,f3161729-181a-4afb-a44e-6d441b04e4f5}

Please tell me how you can do this?

CodePudding user response：

Often when trying to do clever things with arrays, the easiest thing to do is to first unnest the arrays, do what you need, and then put them back into arrays if needed.

WITH unnested AS (
    SELECT table1_id, UNNEST(column_one) AS column_one, UNNEST(column_two) AS column_two 
    FROM table1
)
INSERT INTO table2 (table2_id, column_one, column_two) 
SELECT
    table1_id, 
    ARRAY_AGG(DISTINCT column_one) FILTER (where column_one IS NOT NULL), 
    ARRAY_AGG(DISTINCT column_two) FILTER (where column_two IS NOT NULL)
FROM unnested 
GROUP BY table1_id;