Postgresql limit in string_agg function

I have a query as follows:

SELECT string_agg(sn::text, ','), product_id
  FROM irmsapp_serializedinventory
  group by product_id;

The result is an aggregated sno field and products. I would like to know if we can limit the concatenations to 5 and leave the rest for the sno field. Currently my sno field is a big list and i want to reduce it to a list of 5 elements(the first 5 elements in the sorted order).

Solution

Rows in a relational database are not "sorted". You need to have some column by which you can sort the result, only then you can specify which rows are the "first five".

Assuming you have e.g. a created_at column that defines the "first five", you can do something like this:

select string_agg(sno::text, ','), product_id
from (
   select product_Id, sno,
          row_number() over (partition by product_id order by created_at desc) as rn
   from irmsapp_serializedinventory
) t
where rn <= 5
group by product_id;

This picks 5 rows per product. If you just want 5 rows regardless of the product_id then just remove the partition by product_id in the window function.

The order by created_at desc is what defines the sort order and what defines which five rows are being used in the aggregation.