Thread: Converting row elements into a arrays?

Postgresql 12.13

Given the sample below, I'm looking for how to generate this output.  It's 
like GROUP BY, but generating an array instead of an aggreate number.
  f1 | f2_array
----+---------
1 | {1,2,3}
   2 | {1,2,3,4}
   3 | {1,2}

The ultimate goal is to somehow use pg_index.indkey to get column names from 
pg_attribute.

create table foo (f1 int, f2 int);
insert into foo values (1, 1);
insert into foo values (1, 2);
insert into foo values (1, 3);
insert into foo values (2, 1);
insert into foo values (2, 2);
insert into foo values (2, 3);
insert into foo values (2, 4);
insert into foo values (3, 1);
insert into foo values (3, 2);

select * from foo order by f1, f2;
  f1 | f2
----+----
   1 |  1
   1 |  2
   1 |  3
   2 |  1
   2 |  2
   2 |  3
   2 |  4
   3 |  1
   3 |  2
(9 rows)



-- 
Born in Arizona, moved to Babylonia.

On 02/03/2023 20:58, Ron wrote:
> Postgresql 12.13
> 
> Given the sample below, I'm looking for how to generate this output.  
> It's like GROUP BY, but generating an array instead of an aggreate number.
>   f1 | f2_array
> ----+---------
> 1 | {1,2,3}
>    2 | {1,2,3,4}
>    3 | {1,2}

Something like this (off the top of my head)? -

    select f1, array_agg(f2) as f2_array group by f1;

Hope that helps (and that it's right!).

Ray.



> 
> The ultimate goal is to somehow use pg_index.indkey to get column names 
> from pg_attribute.
> 
> create table foo (f1 int, f2 int);
> insert into foo values (1, 1);
> insert into foo values (1, 2);
> insert into foo values (1, 3);
> insert into foo values (2, 1);
> insert into foo values (2, 2);
> insert into foo values (2, 3);
> insert into foo values (2, 4);
> insert into foo values (3, 1);
> insert into foo values (3, 2);
> 
> select * from foo order by f1, f2;
>   f1 | f2
> ----+----
>    1 |  1
>    1 |  2
>    1 |  3
>    2 |  1
>    2 |  2
>    2 |  3
>    2 |  4
>    3 |  1
>    3 |  2
> (9 rows)
> 
> 
> 

-- 
Raymond O'Donnell // Galway // Ireland
ray@rodonnell.ie

On 02/03/2023 21:01, Ray O'Donnell wrote:
> On 02/03/2023 20:58, Ron wrote:
>> Postgresql 12.13
>>
>> Given the sample below, I'm looking for how to generate this output. 
>> It's like GROUP BY, but generating an array instead of an aggreate 
>> number.
>>   f1 | f2_array
>> ----+---------
>> 1 | {1,2,3}
>>    2 | {1,2,3,4}
>>    3 | {1,2}
> 
> Something like this (off the top of my head)? -
> 
>     select f1, array_agg(f2) as f2_array group by f1;

   ... from foo ...

R.


-- 
Raymond O'Donnell // Galway // Ireland
ray@rodonnell.ie

On 3/2/23 13:58, Ron wrote:
> Postgresql 12.13
>
> Given the sample below, I'm looking for how to generate this output.  
> It's like GROUP BY, but generating an array instead of an aggreate 
> number.
>  f1 | f2_array
> ----+---------
> 1 | {1,2,3}
>   2 | {1,2,3,4}
>   3 | {1,2}
>
> The ultimate goal is to somehow use pg_index.indkey to get column 
> names from pg_attribute.
>
> create table foo (f1 int, f2 int);
> insert into foo values (1, 1);
> insert into foo values (1, 2);
> insert into foo values (1, 3);
> insert into foo values (2, 1);
> insert into foo values (2, 2);
> insert into foo values (2, 3);
> insert into foo values (2, 4);
> insert into foo values (3, 1);
> insert into foo values (3, 2);
>
> select * from foo order by f1, f2;
>  f1 | f2
> ----+----
>   1 |  1
>   1 |  2
>   1 |  3
>   2 |  1
>   2 |  2
>   2 |  3
>   2 |  4
>   3 |  1
>   3 |  2
> (9 rows)
>

In which environment are you accessing that array?  psql only?

>
>

On Thu, Mar 2, 2023 at 1:58 PM Ron <ronljohnsonjr@gmail.com> wrote:

Postgresql 12.13

Given the sample below, I'm looking for how to generate this output.  It's
like GROUP BY, but generating an array instead of an aggreate number.

Group By creates groups, that's it.  How you aggregate the data that are in those groups is determined by which function you call.  Sure, a function like "count()" produces a single number, but there are other functions.  Even a whole documentation section listing them.

https://www.postgresql.org/docs/current/functions-aggregate.html

David J.

On 3/2/23 15:45, Rob Sargent wrote:
> On 3/2/23 13:58, Ron wrote:
>> Postgresql 12.13
>>
>> Given the sample below, I'm looking for how to generate this output.  
>> It's like GROUP BY, but generating an array instead of an aggreate number.
>>  f1 | f2_array
>> ----+---------
>> 1 | {1,2,3}
>>   2 | {1,2,3,4}
>>   3 | {1,2}
>>
>> The ultimate goal is to somehow use pg_index.indkey to get column names 
>> from pg_attribute.
>>
>> create table foo (f1 int, f2 int);
>> insert into foo values (1, 1);
>> insert into foo values (1, 2);
>>
[snip]
>
> In which environment are you accessing that array?  psql only?

Correct.

-- 
Born in Arizona, moved to Babylonia.

On 3/2/23 15:01, Ray O'Donnell wrote:
> On 02/03/2023 20:58, Ron wrote:
>> Postgresql 12.13
>>
>> Given the sample below, I'm looking for how to generate this output.  
>> It's like GROUP BY, but generating an array instead of an aggreate number.
>>   f1 | f2_array
>> ----+---------
>> 1 | {1,2,3}
>>    2 | {1,2,3,4}
>>    3 | {1,2}
>
> Something like this (off the top of my head)? -
>
>    select f1, array_agg(f2) as f2_array group by f1;
>
> Hope that helps (and that it's right!).

That did it.

-- 
Born in Arizona, moved to Babylonia.

On 3/2/23 15:45, Rob Sargent wrote:

On 3/2/23 13:58, Ron wrote:

Postgresql 12.13

Given the sample below, I'm looking for how to generate this output.  It's like GROUP BY, but generating an array instead of an aggreate number.
 f1 | f2_array
----+---------
1 | {1,2,3}
  2 | {1,2,3,4}
  3 | {1,2}

The ultimate goal is to somehow use pg_index.indkey to get column names from pg_attribute.

create table foo (f1 int, f2 int);
insert into foo values (1, 1);
insert into foo values (1, 2);

[snip]

In which environment are you accessing that array?  psql only?

Correct.