8

I'm not so good with SQL (PostgreSQL). Here is what I want to do:

I have a table, fields:

id SERIAL
inet INET
ports integer[]

 id |    inet    | ports 
----+------------+------------
  2 | 1.2.2.1    | {80}
  1 | 1.2.3.4    | {80,12}
  ...

How can I

  1. get all used "ports" values in this table: 80, 12
  2. count how many inet addresses are on specific port:

Like this:

  port  | count
--------+------------
 12     | 1
 80     | 2
  ...

If anyone is looking for a Django version of it:

class Unnest(Func):
    function = 'UNNEST'

Model.objects \
.annotate(port=Unnest('ports', distinct=True)) \
.values('port') \
.annotate(count=Count('port')) \
.order_by('-count', '-port')
9

You can use UNNEST.

select unnest(ports) as port, count(*) from foo group by port;

Using more than one UNNEST in the same query (or the same select list, anyway) is confusing and is probably best avoided.

3

It's cleaner to use set returning functions in the FROM clause where possible. The SQL standard does not allow them in the SELECT list. And it's almost always possible since we have LATERAL joins.

SELECT port, count(*) AS ct
FROM   tbl t, unnest(t.ports) AS port  -- implicit LATERAL join
GROUP  BY port;

But I have to concede that the "quick-and-dirty" variant @Jeff provided is typically faster.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.