Thread: IP address, subnet query behaves wrong for /32
Evenin'
What I discovered just lately is a nice feature from pgsql that I can test
if a specific IP address falls within a supplied subnet:
myserver=# select inet '192.168.0.1' << '192.168.0.0/24'::inet as ip;
ip
----
t
(1 row)
But what I don't understand is why pgsql doesn't behave correctly when testing for a /32 subnet:
(it works for /31 correctly though)
myserver=# select inet '192.168.0.1' << '192.168.0.1/32'::inet as ip;
ip
----
f
From a network engineering point of view this should also return "true" and not false.
Has this been fixed in recent versions? I'm using 9.2.8 right now….
thanks in advance
richard
Evenin'What I discovered just lately is a nice feature from pgsql that I can testif a specific IP address falls within a supplied subnet:But what I don't understand is why pgsql doesn't behave correctly when testing for a /32 subnet:(it works for /31 correctly though)From a network engineering point of view this should also return "true" and not false.
select inet '192.168.0.1' <<= '192.168.0.1/32'::inet as ip;
ip
---
t
My best explanation is that since there is no network part on a /32 address there is no concept of "contained within the network" to match against. The added equality check allows for that condition to be matched.
David J.
On 08/06/2015 09:47 AM, Richard RK. Klingler wrote: > Evenin' > > What I discovered just lately is a nice feature from pgsql that I can test > if a specific IP address falls within a supplied subnet: > > myserver=# select inet '192.168.0.1' << '192.168.0.0/24'::inet as ip; > > ip > > ---- > > t > > (1 row) > > > > But what I don't understand is why pgsql doesn't behave correctly when > testing for a /32 subnet: > (it works for /31 correctly though) > > myserver=# select inet '192.168.0.1' << '192.168.0.1/32'::inet as ip; > > ip > > ---- > > f > > > From a network engineering point of view this should also return "true" > and not false. http://www.postgresql.org/docs/9.2/interactive/functions-net.html "The operators <<, <<=, >>, and >>= test for subnet inclusion." http://www.postgresql.org/docs/9.2/interactive/datatype-net-types.html#DATATYPE-INET " If the netmask is 32 and the address is IPv4, then the value does not indicate a subnet, only a single host." So it is behaving as documented. > > Has this been fixed in recent versions? I'm using 9.2.8 right now…. > > > > thanks in advance > richard > > -- Adrian Klaver adrian.klaver@aklaver.com
http://www.postgresql.org/docs/9.2/interactive/functions-net.html
"The operators <<, <<=, >>, and >>= test for subnet inclusion."
http://www.postgresql.org/docs/9.2/interactive/datatype-net-types.html#DATATYPE-INET
" If the netmask is 32 and the address is IPv4, then the value does not indicate a subnet, only a single host."
So it is behaving as documented.
This seems overly simplified given that "<<=" will indeed match two host specifications.
David J.
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Thu, Aug 6, 2015 at 10:02 AM, Adrian Klaver <adrian.klaver@aklaver.com>
> wrote:
>> " If the netmask is 32 and the address is IPv4, then the value does not
>> indicate a subnet, only a single host."
>>
>> So it is behaving as documented.
> This seems overly simplified given that "<<=" will indeed match two host
> specifications.
No, only one. There is no difference between '192.168.0.1'::inet and
'192.168.0.1/32'::inet; they're the same value. The first notation
is merely a shorthand for the second.
regards, tom lane
Thanks to all for the clarifications... I'm looking at this form an application perspective... as this would greatly enhance an IPAM database web application. Sad there is no direct IP address sorting function like in MySQL (o; cheers from .ch richard Am [DATE] schrieb "pgsql-sql-owner@postgresql.org im Auftrag von Tom Lane" <[ADDRESS]>: >"David G. Johnston" <david.g.johnston@gmail.com> writes: >> On Thu, Aug 6, 2015 at 10:02 AM, Adrian Klaver <adrian.klaver@aklaver.com> >> wrote: >>> " If the netmask is 32 and the address is IPv4, then the value does not >>> indicate a subnet, only a single host." >>> >>> So it is behaving as documented. > >> This seems overly simplified given that "<<=" will indeed match two host >> specifications. > >No, only one. There is no difference between '192.168.0.1'::inet and >'192.168.0.1/32'::inet; they're the same value. The first notation >is merely a shorthand for the second. > > regards, tom lane > > >-- >Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) >To make changes to your subscription: >http://www.postgresql.org/mailpref/pgsql-sql
On Thu, Aug 06, 2015 at 07:35:19PM +0000, Richard RK. Klingler wrote: > Thanks to all for the clarifications... > > I'm looking at this form an application perspective... > as this would greatly enhance an IPAM database web application. > > Sad there is no direct IP address sorting function like in MySQL (o; > > > cheers from .ch > richard > What about: select * from table order by inet(IP-ADDRESS); Seems pretty straight-forward. What does MySQL do? Regards, Ken
On 08/06/2015 12:35 PM, Richard RK. Klingler wrote: > Thanks to all for the clarifications... > > I'm looking at this form an application perspective... > as this would greatly enhance an IPAM database web application. > > Sad there is no direct IP address sorting function like in MySQL (o; http://www.postgresql.org/docs/9.2/static/datatype-net-types.html "When sorting inet or cidr data types, IPv4 addresses will always sort before IPv6 addresses, including IPv4 addresses encapsulated or mapped to IPv6 addresses, such as ::10.2.3.4 or ::ffff:10.4.3.2." So: test=# create table inet_test(i_fld inet); CREATE TABLE test=# insert into inet_test values ('192.0.1.2'); INSERT 0 1 test=# insert into inet_test values ('192.0.0.3'); INSERT 0 1 test=# insert into inet_test values ('192.0.1.165'); INSERT 0 1 test=# select * from inet_test order by i_fld ; i_fld ------------- 192.0.0.3 192.0.1.2 192.0.1.165 > > > cheers from .ch > richard > > > > > Am [DATE] schrieb "pgsql-sql-owner@postgresql.org im Auftrag von Tom Lane" <[ADDRESS]>: > >> "David G. Johnston" <david.g.johnston@gmail.com> writes: >>> On Thu, Aug 6, 2015 at 10:02 AM, Adrian Klaver <adrian.klaver@aklaver.com> >>> wrote: >>>> " If the netmask is 32 and the address is IPv4, then the value does not >>>> indicate a subnet, only a single host." >>>> >>>> So it is behaving as documented. >> >>> This seems overly simplified given that "<<=" will indeed match two host >>> specifications. >> >> No, only one. There is no difference between '192.168.0.1'::inet and >> '192.168.0.1/32'::inet; they're the same value. The first notation >> is merely a shorthand for the second. >> >> regards, tom lane >> >> >> -- >> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-sql > -- Adrian Klaver adrian.klaver@aklaver.com
On 8/6/15 3:35 PM, Richard RK. Klingler wrote: > Thanks to all for the clarifications... > > I'm looking at this form an application perspective... > as this would greatly enhance an IPAM database web application. > > Sad there is no direct IP address sorting function like in MySQL (o; Many people prefer ip4r (https://github.com/RhodiumToad/ip4r) over the built-in types. You might find that they work better for you.