CODEWITHSUNDEEP
postgresql
Mariano L
Mariano L

Reputation: 1879

Get MAX and MIN ip from subnet/mask in postgres

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

Upvotes: 1

Views: 3090

Answers (2)

klin
klin

Reputation: 121604

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
$$;

Upvotes: 0

Philip Couling
Philip Couling

Reputation: 14893

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.

Upvotes: 6

Related Questions