Thread: converting 1 dimensional array to 2 dimensional array
Hi, need help with some syntax.
Currently we have a column of type text[]. It is a 1 dimensional array of text values. Each of them we have formatted in such a way that it can be split into two values. We are trying to convert it into a two dimensional array but are unsure how to do that.
i.e. converting:
{a^^val1, b^^val2, c^^val3}
to
{{a,val1}, {b,val2}, {c,val3}}
I can do
Select array(select '{"' || replace(unnest(col1), '^^', '","') || '"}') from table;
Select array(select '{"' || replace(unnest(col1), '^^', '","') || '"}') from table;
But that still gives me one dimensional text array. I can't cast it to text[] because there are no array of arrays. How do I get this into a 2 dimensional array?
On Mon, Mar 12, 2012 at 3:31 PM, Nicholas Wilson <nwilson5@gmail.com> wrote: > Hi, need help with some syntax. > > Currently we have a column of type text[]. It is a 1 dimensional array of > text values. Each of them we have formatted in such a way that it can be > split into two values. We are trying to convert it into a two dimensional > array but are unsure how to do that. > > i.e. converting: > {a^^val1, b^^val2, c^^val3} > to > {{a,val1}, {b,val2}, {c,val3}} > > I can do > Select array(select '{"' || replace(unnest(col1), '^^', '","') || '"}') from > table; > > But that still gives me one dimensional text array. I can't cast it to > text[] because there are no array of arrays. How do I get this into a 2 > dimensional array? It turns out to be a real bugaboo to great >1d arrays except for the following methods: *) textual definition *) nested array[] (this method only works well when your input list is constant) *) select array[1d array] (works fine but you have no control over array structure) *) iterative approaches in plpgsql which are slow and suck This is the best 1-liner I could come up with. It's going to be really sensitive to input data and will break for example if there are any double quotes in your field. If the number of array elements is fixed there's a much better way. postgres=# select replace(array(select string_to_array(unnest('{a^^val1, b^^val2, c^^val3}'::text[]), '^^')::text)::text, '"', '')::text[]; replace ------------------------------ {{a,val1},{b,val2},{c,val3}} postgres=# select array_dims(replace(array(select string_to_array(unnest('{a^^val1, b^^val2, c^^val3}'::text[]), '^^')::text)::text, '"', '')::text[]); array_dims ------------ [1:3][1:2] merlin