Postgres Pro
Downloads
Home   >   mailing lists

Thread: How to access array element in pgsql after array_agg

How to access array element in pgsql after array_agg

From
Condor
Date:
Hello,

I wanna ask how I can access array element in array_agg ?

I do select array_agg(ids) from x;
in ids I have int and result is :

        array_agg
       -------------
     {3843,2,3543,33}

I want to access one element or first one direct in sql query like:

select array_agg(ids)[1] from x;

and to receive int value 2

Any hints how I can do it ?

And also for performance did that is good I to do it in SQL or I should
do it in language that I use ? The result in query will return about 2,
3 million rows.


Thank you,

Cheers,
Hristo S.

Re: How to access array element in pgsql after array_agg

From
François Beausoleil
Date:
Le 2014-06-17 à 14:22, Condor <condor@stz-bg.com> a écrit :

> I do select array_agg(ids) from x;
> in ids I have int and result is :
>
>       array_agg
>      -------------
>    {3843,2,3543,33}
>
> I want to access one element or first one direct in sql query like:
>
> select array_agg(ids)[1] from x;
>
> and to receive int value 2
>
> Any hints how I can do it ?

This works for me in 9.1:

psql (9.1.13)
Type "help" for help.

svanalytics=> select (array_agg(x))[1] from (values(3843),(2),(3543),(33)) t1(x) ;
 array_agg
-----------
      3843

Note the use of the extra parens around the array_agg call. This is probably a parser issue more than anything else.

> And also for performance did that is good I to do it in SQL or I should do it in language that I use ? The result in
querywill return about 2, 3 million rows. 

Do you mean you will have an array of 2, 3 million elements, or 2, 3 million rows with a couple dozen elements each?
I’mnot sure which will be easier / faster. I routinely work with million element result sets (rows) and have no issues. 

Hope that helps!
François

Re: How to access array element in pgsql after array_agg

From
Condor
Date:
On 17-06-2014 22:44, François Beausoleil wrote:
> Le 2014-06-17 à 14:22, Condor <condor@stz-bg.com> a écrit :
>
>> I do select array_agg(ids) from x;
>> in ids I have int and result is :
>>
>>       array_agg
>>      -------------
>>    {3843,2,3543,33}
>>
>> I want to access one element or first one direct in sql query like:
>>
>> select array_agg(ids)[1] from x;
>>
>> and to receive int value 2
>>
>> Any hints how I can do it ?
>
> This works for me in 9.1:
>
> psql (9.1.13)
> Type "help" for help.
>
> svanalytics=> select (array_agg(x))[1] from
> (values(3843),(2),(3543),(33)) t1(x) ;
>  array_agg
> -----------
>       3843
>
> Note the use of the extra parens around the array_agg call. This is
> probably a parser issue more than anything else.
>
>> And also for performance did that is good I to do it in SQL or I
>> should do it in language that I use ? The result in query will return
>> about 2, 3 million rows.
>
> Do you mean you will have an array of 2, 3 million elements, or 2, 3
> million rows with a couple dozen elements each? I’m not sure which
> will be easier / faster. I routinely work with million element result
> sets (rows) and have no issues.
>
> Hope that helps!
> François


Ah, double brackets and works, I did not expect and try
select (array_agg(ids))[1] from x;
to work but its work.
I try:
select array_agg(ids)[1] from x;


Thank you