How to access array element in pgsql after array_agg

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general