Set Limit To Array_agg()

I have the following Postgres query: SELECT array_agg('Esns'.id ) FROM public.'Esns', public.'PurchaseOrderItems' WHERE 'Esns'.'PurchaseOrderItemId' = 'PurchaseOrderIt

Solution 1:

select id[1], id[2]
from (
    SELECT array_agg("Esns".id ) as id
    FROMpublic."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_array
FROMpublic."Esns", 
     public."PurchaseOrderItems"WHERE"Esns"."PurchaseOrderItemId" = "PurchaseOrderItems".id 
    AND"PurchaseOrderItems"."GradeId"=2

Solution 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 
   FROMpublic."Esns" e
   JOINpublic."PurchaseOrderItems" p ON p.id = e."PurchaseOrderItemId"WHERE  p."GradeId" = 2 
   --  ORDERBY ???
   LIMIT  4  -- or2?
   )

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.