CODEWITHSUNDEEP
sqlarrayspostgresqlrdbms
Nicholas Leonard
Nicholas Leonard

Reputation: 2696

Vertical to Horizontal?

I have a PostgreSQL table that looks like this:

A -> B
A -> C
A -> G
A -> H
B -> O
B -> K

Where "->" separates two columns where the first points to the second (hyperlinks). Now I would like to take all distinct values in the first column and assign them an ARRAY containing all values to which they point to in the second column, thereby reducing index size and help me get closer to where I wish to get.

Hence, I wish to ARRAYify a single columned result set of [many] rows. If I could call a function like arrayify(SELECT column2 FROM table WHERE column1 = 'A') that would make my life so easy.

Anyone or anymany knows?

Thx

Upvotes: 1

Views: 773

Answers (1)

Chris Travers
Chris Travers

Reputation: 26464

It depends on which version of PostgreSQL you are using. If you are using recent versions (anything supported at present), you can use array_agg(col)

For example:

select array_agg(test) from test;
                                       array_agg                                

--------------------------------------------------------------------------------
-------
 {4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,
32,33}
(1 row)

If you are using an older version, you may need to write your own array_agg aggregate or workalike. Here's a way to write one called "as_array":

CREATE AGGREGATE as_array (
        BASETYPE = ANYELEMENT,
        STYPE = ANYARRAY,
        SFUNC = ARRAY_APPEND,
        INITCOND = '{}'
);

Upvotes: 1

Related Questions