Question 241109 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to read first N of values from Postgres array

I have ARRAY_AGG( distinct CAST (asset_group_id AS text)) which returns array of hundreds of elements but I want only first 100 elements.

I didn’t find any such function available in postgres array documentation.

How can I achieve it in the postgres?

Tags:

2

Answers


  1. 0

    Just use a CTE to limit to 100 elements, then create the array, like

    with elements as(
select distinct cast(asset_group_id as text) id 
from my_table limit 100)
select array_agg(id) from elements;

    And using window functions would be like

    select array_agg(id)  
from (select distinct cast(asset_group_id as text) id, 
row_number() over () from my_table) t
where row_number <=100;

    by the way in this case is better performant the common table expression.

    Login or Signup to reply.
  2. 0

    PostgreSQL arrays support subscripts and slices.
    In your case, to get the first 100 elements: demo

    (ARRAY_AGG( distinct asset_group_id::text) )[:100]

    It is possible to omit the lower-bound and/or upper-bound of a slice specifier; the missing bound is replaced by the lower or upper limit of the array’s subscripts.

    It’s better for static, existing arrays. In your case, you’re building one, collecting all your distinct elements and this discards everything past that first 100, wasting the rest of the work put into finding all other distinct values. It’s better to not collect more than you need in the first place, as Pepe N O suggested, using a limit in a CTE:

    with cte as (select distinct asset_group_id::text 
             from tests 
             limit 100)
select ARRAY_AGG(asset_group_id) 
from cte;

    or a plain subquery

    select ARRAY_AGG(asset_group_id) 
from (select distinct asset_group_id::text 
      from tests 
      limit 100) as subquery;

    The demo shows the performance difference between just grabbing the first distinct 100 and no more, versus having to collect all distincts and then discarding everything past the first 100.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search