unnest and string_to_array on two columns

[Michael Graham <mgraham@xxxxxxxxx>]

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@xxxxxxxxx>



-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general