How to get an array in postgres where the array size is greater than 1

Question

I have a table that looks like this:

val | fkey | num
------------------
1   |  1   | 1
1   |  2   | 1  
1   |  3   | 1  
2   |  3   | 1 

What I would like to do is return a set of rows in which values are grouped by 'val', with an array of fkeys, but only where the array of fkeys is greater than 1. So, in the above example, the return would look something like:

1 | [1,2,3]

I have the following query aggregates the arrays:

SELECT val, array_agg(fkey)
FROM mytable
GROUP BY val;

But this returns something like:

1 | [1,2,3]
2 | [3]

What would be the best way of doing this? I guess one possibility would be to use my existing query as a subquery, and do a sum / count on that, but that seems inefficient. Any feedback would really help!

Answer 1

Use Having clause to filter the groups which is having more than fkey

SELECT val, array_agg(fkey)
FROM mytable
GROUP BY val
Having Count(fkey) > 1

Answer 2

Using the HAVING clause as @Fireblade pointed out is probably more efficient, but you can also leverage subqueries:

SQLFiddle: Subquery

SELECT * FROM (
   select   val, array_agg(fkey) fkeys
   from     mytable
   group by val
) array_creation
WHERE array_length(fkeys,1) > 1

You could also use the array_length function in the HAVING clause, but again, @Fireblade has used count(), which should be more efficient. Still:

SQLFiddle: Having Clause

SELECT   val, array_agg(fkey) fkeys
FROM     mytable
GROUP BY val
HAVING   array_length(array_agg(fkey),1) > 1

This isn't a total loss, though. Using the array_length in the having can be useful if you want a distinct list of fkeys:

SELECT val, array_agg(DISTINCT fkey) fkeys

There may still be other ways, but this method is more descriptive, which may allow your SQL to be easier to understand when you come back to it, years from now.