Thread: unnest and string_to_array on two columns

Hi all,

I'm trying to migrate an old (and sucky) schema to a new one and I'm
having some difficulties coming up with a sane select.

I have basically id, a, and b where a and b contain a list of flags like

id | a    | b      |
--------------------
1  | abc  | abcdef |

and what to convert this to multiple ids with single flags, like:

id | a    | b    |
------------------
1  | a    | a    |
1  | b    | b    |
1  | c    | c    |
1  | NULL | d    |
1  | NULL | e    |
1  | NULL | f    |

My first attempt was

SELECT id, unnest(string_to_array(a,NULL)),
unnest(string_to_array(b,NULL)) FROM foo;

But this causes the shorter string to be repeated until it is the same
length as the shorter string.  In the end I have managed to get the
behaviour that I want but the select is horrible:

SELECT COALESCE(aa.id,bb.id) AS id,
    aa.unnest AS aaaaa,
    bb.unnest AS bbbbb FROM
    (
        SELECT *, row_number() OVER() FROM
        (
             SELECT id,unnest(string_to_array(a,NULL)) FROM foo
        ) AS a
    ) AS aa
    FULL JOIN
    (
        SELECT *, row_number() OVER() FROM
        (
            SELECT id,unnest(string_to_array(b,NULL)) FROM foo
        ) AS b
    ) AS bb
    ON aa.row_number=bb.row_number AND aa.id=bb.id;

So I was wondering if anyone had any better solutions.

Thanks,
--
Michael Graham <mgraham@bloxx.com>

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Michael Graham
Sent: Tuesday, October 25, 2011 4:36 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] unnest and string_to_array on two columns

Hi all,

I'm trying to migrate an old (and sucky) schema to a new one and I'm having some difficulties coming up with a sane
select.

I have basically id, a, and b where a and b contain a list of flags like

id | a    | b      |
--------------------
1  | abc  | abcdef |

and what to convert this to multiple ids with single flags, like:

id | a    | b    |
------------------
1  | a    | a    |
1  | b    | b    |
1  | c    | c    |
1  | NULL | d    |
1  | NULL | e    |
1  | NULL | f    |

My first attempt was

SELECT id, unnest(string_to_array(a,NULL)),
unnest(string_to_array(b,NULL)) FROM foo;

But this causes the shorter string to be repeated until it is the same length as the shorter string.  In the end I have
managedto get the behaviour that I want but the select is horrible: 

SELECT COALESCE(aa.id,bb.id) AS id,
    aa.unnest AS aaaaa,
    bb.unnest AS bbbbb FROM
    (
        SELECT *, row_number() OVER() FROM
        (
             SELECT id,unnest(string_to_array(a,NULL)) FROM foo
        ) AS a
    ) AS aa
    FULL JOIN
    (
        SELECT *, row_number() OVER() FROM
        (
            SELECT id,unnest(string_to_array(b,NULL)) FROM foo
        ) AS b
    ) AS bb
    ON aa.row_number=bb.row_number AND aa.id=bb.id;

So I was wondering if anyone had any better solutions.

Thanks,
--
Michael Graham <mgraham@bloxx.com>

----------------- /Original Message -----------------
Same solution but using CTEs.

WITH val_src AS (
SELECT * FROM (VALUES (1, 'a,b,c','a,b,c,d,e')) vals (id, a, b)
), a_expanded AS (
   SELECT *, ROW_NUMBER() OVER () AS row_index FROM (
    SELECT id, unnest(string_to_array(a, ',')) AS a_item FROM val_src
  ) a_src
), b_expanded aS (
   SELECT *, ROW_NUMBER() OVER () AS row_index FROM (
    SELECT id, unnest(string_to_array(b, ',')) AS b_item FROM val_src
  ) b_src
)
SELECT *
FROM b_expanded NATURAL FULL OUTER JOIN a_expanded;

The only other thought would be to limit your first query to [pseudocode] "WHERE ROW_NUMBER() OVER () <= MIN(b.length,
a.length)"and the UNION the remainder of A and B where the row number is "> MIN(b.length, a.length)".  This seems worse
though.

David J.