Set a limit on array_agg ()

Question

I have the following Postgres request:

SELECT array_agg("Esns".id ) FROM public."Esns", public."PurchaseOrderItems" WHERE "Esns"."PurchaseOrderItemId" = "PurchaseOrderItems".id AND "PurchaseOrderItems"."GradeId"=2 LIMIT 2; 

The limit will affect the lines. I want it to limit array_agg() to 2 elements. The following query works, but I get my output with each entry in quotation marks:

 SELECT array_agg ("temp") FROM ( SELECT "Esns".id FROM public."Esns", public."PurchaseOrderItems" WHERE "Esns"."PurchaseOrderItemId" = "PurchaseOrderItems".id AND "PurchaseOrderItems"."GradeId"=2 LIMIT 4 ) as "temp" ; 

This gives me the following conclusion

 {(13),(14),(15),(12)} 

Any ideas?

Answer 1

 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 result to be like an array:

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

Answer 2

Brackets (not "quotation marks") as a result are decorators for string literals. You are building an array of whole rows (which contain only one column). Instead, only aggregate the column .

In addition, directly building an array from a query result is usually 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 something ORDER BY if you want a stable result and / or select specific rows. Otherwise, the result will be arbitrary and may change with each subsequent call.

During this, I rewrote the request with explicit JOIN syntax, which is usually preferable, and used rable aliases to simplify.