PostgreSQL - string_agg with a limited number of elements

Question

Is it possible to limit the number of elements in the following string_agg function?

  string_agg(distinct(tag),', ') 

Answer 1

I do not know that you can limit it in the string_agg() function. You can limit it in other ways:

 select postid, string_agg(distinct(tag), ', ') from table t group by postid 

Then you can do:

 select postid, string_agg(distinct (case when seqnum <= 10 then tag end), ', ') from (select t.*, dense_rank() over (partition by postid order by tag) as seqnum from table t ) t group by postid 

Answer 2

In limit the number of elements in the following string_agg() use LIMIT in the subquery:

 SELECT string_agg(tag, ', ') AS tags FROM ( SELECT DISTINCT tag FROM tbl -- ORDER BY tag -- optionally order to get deterministic result LIMIT 123 -- add your limit here ) sub; 

Note that a subquery is not a performance issue at all. On the contrary, it is usually faster , even if you do not impose a maximum number on LIMIT , because groupwise DISTINCT in an aggregated function is more expensive than doing it in a subquery for all rows at once.

Or, to get the "100 most common tags":

 SELECT string_agg(tag, ', ') AS tags FROM ( SELECT tag FROM tbl GROUP BY tag ORDER BY count(*) DESC LIMIT 100 ) sub; 

Answer 3

There are two more ways.

1) make an array of strings, limit it, and then combine into a string:

 SELECT array_to_string((array_agg(tag))[1:3], ', ') FROM tbl 

("array [1: 3]" means: take elements from 1 to 3 from the array)

2) concatenate the strings into a string without restrictions, then use the "substring" to crop it:

 string_agg(distinct(tag),',') 

If you know that the tag field cannot contain a character, then you can select all the text before the nth appearance of yours ,

 SELECT substring( string_agg(DISTINCT tag, ',') from '(?:[^,]+,){1,3}') FROM tbl 

This substring will select 3 or less lines divided by ,