2

Consider query:

select min(d) from temp;
select max(d) from temp;

Either one, I get an error like:

# select max(d) from temp;
ERROR:  function max(inet) does not exist
LINE 1: select max(d) from temp;
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

So, I create a table called temp and populate it:

create table temp (d inet);
insert into temp (d) values ('1.1.10.2');
insert into temp (d) values ('1.1.10.10');
insert into temp (d) values ('1.1.10.20');
insert into temp (d) values ('1.1.10.100');

# select * from temp order by d;

     d      
------------
 1.1.10.2
 1.1.10.10
 1.1.10.20
 1.1.10.100
(4 rows)

So, I can use host() to convert to text, but that produces incorrect answer:

select min(host(d)) from temp;

That is because it is doing a text 'min' function, which is this ordering:

# select host(d) as r from temp order by r;
     r      
------------
 1.1.10.10
 1.1.10.100
 1.1.10.2
 1.1.10.20
(4 rows)

Is there a min() and max() function for ip types in postgres? There are ways to trick it (convert to int and compare, or do a order by with a limit). I am more interested in proper min() and max(). Thank you SO!

-g

Greg
  • 6,571
  • 2
  • 27
  • 39
  • i read this http://www.postgresql.org/message-id/CAJrrPGdeZpfTPR6EkGgM6A4bUG7BVVToXsMgqJ6rg7R1JRUPVg@mail.gmail.com and i think there is already a patch fixed this. but i tried on PG9.3 it failed, my friend tried on 9.4 it failed too.i think what you can do is mapping it into int and use min() on that – Raffaello.D.Huke Feb 02 '16 at 02:58

2 Answers2

1

You can use existing functions network_smaller(inet, inet) and network_larger(inet, inet) to define your own aggregates:

create aggregate min (inet) (
    sfunc = network_smaller,
    stype = inet);

create aggregate max (inet) (
    sfunc = network_larger,
    stype = inet);

select min(d) min, max(d) max
from temp;

   min    |    max     
----------+------------
 1.1.10.2 | 1.1.10.100
(1 row)
klin
  • 112,967
  • 15
  • 204
  • 232
  • thank you! I am aware of this sort of work around. I had just read that min() and max() exist after 9.3, but I just can't find them. I was hoping for an answer that will tell me how I can use min() and max(). – Greg Feb 02 '16 at 03:17
  • I've found a better solution, see the edited answer. – klin Feb 02 '16 at 03:27
  • Is this really not what you were looking for? The solution is as close as possible to the core of Postgres. – klin Feb 03 '16 at 21:13
  • Thank you for the answer. I really thought I was overlooking something that was/should be in postgres itself. I guess you did answer my question: min() and max() aren't available for inet types. Weird. – Greg Feb 04 '16 at 00:57
0

i checked again, pg still not support min(inet). i guess what you can do is make another column to mapping the inet like replace(r,'.','') and min() it.

Raffaello.D.Huke
  • 552
  • 2
  • 9
  • This will not work with IPs like 0.1.0.1 . You definitely need the value representation of that ip. – Micha Mar 09 '21 at 10:46