问题描述
考虑查询:
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.
因此,我创建一个名为temp的表并填充它:
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)
因此,我可以使用host()转换为文本,但这会产生错误的答案:
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)
postgres中的ip类型是否有min()和max()函数?有多种方法可以欺骗它(转换为int并进行比较,或通过限制进行排序).我对适当的min()和max()感兴趣.谢谢你!
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
推荐答案
您可以使用现有函数network_smaller(inet, inet)
和network_larger(inet, inet)
定义自己的聚合:
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)
这篇关于如何使用postgres查询最小或最大inet/cidr的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!