CODEWITHSUNDEEP
sqlpostgresqlaggregate-functions
nickbusted
nickbusted

Reputation: 1109

PostgreSQL - string_agg with limited number of elements

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

 string_agg(distinct(tag),', ')

Upvotes: 21

Views: 28203

Answers (6)

Willi
Willi

Reputation: 407

Try

array_sample(array_agg(column),count)

if you don't care which rows are given.

Upvotes: 0

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656241

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;

Upvotes: 18

alexkovelsky
alexkovelsky

Reputation: 4190

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)

  1. 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 exclude trailing , just add rtrim:

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

Upvotes: 31

samthebrand
samthebrand

Reputation: 3090

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)

Upvotes: 3

user2220029
user2220029

Reputation: 1

Use IN to filter

Like This:

Select
  primaryID,
  String_Agg(email, '|') As Email
From
  contacts
Where
  contactID In (Select filter.contactID
  From contacts filter
  Where filter.primaryID = contacts.primaryID
  Order By filter.contactID)
Group By
  primaryID;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269443

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

Upvotes: 11

Related Questions