PostgreSQL - string_agg with limited number of elements

I am not aware 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

Nest your string_agg clause in a split_part, including the delimiter as the second argument, and the number of elements you need as the final argument. Like so:

split_part(string_agg(distinct(tag),', '), ', ', 1)

There are two more ways.

1) make an array from rows, limit it, and then concatenate into string:

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

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

2) concatenate rows into string without limit, then use "substring" to trim it:

string_agg(distinct(tag),',')

If you know that your "tag" field cannot contain , character then you can select all text before nth occurence of your ,

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

This substring will select 3 or less strings divided by ,

To "limit the number of elements in the following string_agg()", use LIMIT in a 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;

The subquery is no problem for performance at all. On the contrary, this is typically faster, even if you don't impose a maximum number with LIMIT, because the group-wise DISTINCT in the aggregate 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;