Set limit to array_agg() Set limit to array_agg() postgresql postgresql

Set limit to array_agg()


select id[1], id[2]from (    SELECT array_agg("Esns".id ) as id    FROM public."Esns",          public."PurchaseOrderItems"     WHERE         "Esns"."PurchaseOrderItemId" = "PurchaseOrderItems".id         AND "PurchaseOrderItems"."GradeId"=2 ) s

or if you want the output as array you can slice it:

SELECT (array_agg("Esns".id ))[1:2] as id_arrayFROM public."Esns",      public."PurchaseOrderItems" WHERE     "Esns"."PurchaseOrderItemId" = "PurchaseOrderItems".id     AND "PurchaseOrderItems"."GradeId"=2 


The parentheses (not "quotes") in the result are decorators for the row literals. You are building an array of whole rows (which happen to contain only a single column). Instead, aggregate only the column.

Also, direct array construction from a query result is typically simpler and faster:

SELECT ARRAY (   SELECT e.id    FROM   public."Esns" e   JOIN   public."PurchaseOrderItems" p ON p.id = e."PurchaseOrderItemId"   WHERE  p."GradeId" = 2    --  ORDER BY ???   LIMIT  4  -- or 2?   )

You need to ORDER BY something if you want a stable result and / or pick certain rows. Otherwise the result is arbitrary and can change with every next call.

While being at it I rewrote the query with explicit JOIN syntax, which is generally preferable, and used table aliases to simplify.