Get MAX and MIN ip from subnet/mask in postgres

Question

I have the following issue:

Right now I have a table with the subnet/mask information (example 192.168.1.0 / 255.255.255.0 ) .. but I need to obtain the MAX and MIN IP from this subnet:

192.168.1.0 / 192.168.1.255

I've found this answer:

how to query for min or max inet/cidr with postgres

But it seems that:

network_smaller(inet, inet) and network_larger(inet, inet)

Doesn't exists. Even googling that I can't find any answer for those functions.

Thanks!

Edit:

Version info:

PostgreSQL 9.2.15 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-4), 64-bit

Philip Couling · Answer 1 · 2016-08-25T16:03:44.870

I don't think that question is relavent to your needs anyway. The min and max defined there are similar to the SQL min() and max() functions for finding the smallest / largest in a table, not the smallest / largest in a subnet.

I'm not generally a fan of relying on undocumented features. They may be safe but may isn't a word I generally like.

There's a page of documented network functions here: https://www.postgresql.org/docs/current/static/functions-net.html

The two you would need would be:

Min would be network(inet)
Max would be broadcast(inet)

That's because the network name is always the "first" ip in the range and the broadcast address is always the "last" ip in the range.

score 0 · Answer 2 · edited May 23 '17 at 10:29

Don't google, just try:

select network_smaller('192.168.0.9'::inet, '192.168.0.11'::inet);

 network_smaller 
-----------------
 192.168.0.9
(1 row)

Postgres has more than 2,600 internal functions. Most of them are useful for creating operator classes of various types. Not all of them are described in the documentation, but they are all generally available.

You can find them using pgAdmin III in pg_catalog. You only need to set the option: File -> Options -> UI Miscellaneous -> Show System Objects in treeview.

The aggregate functions min(inet) and max(inet) has been introduced in Postgres 9.5:

with test(ip) as (
values 
    ('192.168.0.123'::inet),
    ('192.168.0.12'),
    ('192.168.0.1'),
    ('192.168.0.125')   
)
select max(ip), min(ip)
from test;

      max      |     min     
---------------+-------------
 192.168.0.125 | 192.168.0.1
(1 row)

See how the aggregate min(inet) is defined (it can be found in pg_catalog):

CREATE AGGREGATE min(inet) (
  SFUNC=network_smaller,
  STYPE=inet,
  SORTOP="<"
);

The question How to query for min or max inet/cidr with postgres concerned Postgres 9.4. In my answer I suggested to use the functions network_smaller(inet, inet) and network_larger(inet, inet). I'm sure they were added for creating aggregate functions min(inet) and max(inet) but for some reasons (maybe oversight) the aggregates appeared only in Postgres 9.5.

In Postgres 9.2 you can create your own functions as substitutes, e.g.

create or replace function inet_larger(inet, inet)
returns inet language sql as $$
    select case when network_gt($1, $2) then $1 else $2 end
$$;

create or replace function inet_smaller(inet, inet)
returns inet language sql as $$
    select case when network_lt($1, $2) then $1 else $2 end
$$;

Well. I've obviously tried that.. ERROR: function network_smaller(inet, inet) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts. — Mariano L, Aug 25 '16 at 15:38
Just a question. How safe is it to rely on such an undocumented feature? — Philip Couling, Aug 25 '16 at 15:54
@PhilipCouling - Can we rely on the internal functions? I think so, as far as all operators, indexes, sorting and most aggregates are based on them. — klin, Aug 25 '16 at 17:48

Get MAX and MIN ip from subnet/mask in postgres

2 Answers2