Skip site navigation (1) Skip section navigation (2)

Re: converting 1 dimensional array to 2 dimensional array

From: Merlin Moncure <mmoncure(at)gmail(dot)com>
To: Nicholas Wilson <nwilson5(at)gmail(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: converting 1 dimensional array to 2 dimensional array
Date: 2012-03-13 14:37:21
Message-ID: CAHyXU0w39GxhNuDonFAbE4uDrz7Eiripx9OPQ5DnuNtBU5w6Nw@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
On Mon, Mar 12, 2012 at 3:31 PM, Nicholas Wilson <nwilson5(at)gmail(dot)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

In response to

pgsql-novice by date

Next:From: JORGE MALDONADODate: 2012-03-13 16:44:59
Subject: Using pg_trgm
Previous:From: James MartensDate: 2012-03-13 08:30:31
Subject: Updating RHEL4 system for PHP; requires Postgresql updates

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group