agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedIP address, subnet query behaves wrong for /32
9+ messages / 6 participants
[nested] [flat]
* IP address, subnet query behaves wrong for /32
@ 2015-08-06 16:47 Richard RK. Klingler <richard@klingler.net>
2015-08-06 17:01 ` Re: IP address, subnet query behaves wrong for /32 David G. Johnston <david.g.johnston@gmail.com>
2015-08-06 17:02 ` Re: IP address, subnet query behaves wrong for /32 Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 2 replies; 9+ messages in thread
From: Richard RK. Klingler @ 2015-08-06 16:47 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: IP address, subnet query behaves wrong for /32
2015-08-06 16:47 IP address, subnet query behaves wrong for /32 Richard RK. Klingler <richard@klingler.net>
@ 2015-08-06 17:01 ` David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 0 replies; 9+ messages in thread
From: David G. Johnston @ 2015-08-06 17:01 UTC (permalink / raw)
To: Richard RK. Klingler <richard@klingler.net>; +Cc: pgsql-sql
On Thu, Aug 6, 2015 at 9:47 AM, Richard RK. Klingler <richard@klingler.net>
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.
>
>
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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: IP address, subnet query behaves wrong for /32
2015-08-06 16:47 IP address, subnet query behaves wrong for /32 Richard RK. Klingler <richard@klingler.net>
@ 2015-08-06 17:02 ` Adrian Klaver <adrian.klaver@aklaver.com>
2015-08-06 17:07 ` Re: IP address, subnet query behaves wrong for /32 David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 1 reply; 9+ messages in thread
From: Adrian Klaver @ 2015-08-06 17:02 UTC (permalink / raw)
To: Richard RK. Klingler <richard@klingler.net>; pgsql-sql
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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: IP address, subnet query behaves wrong for /32
2015-08-06 16:47 IP address, subnet query behaves wrong for /32 Richard RK. Klingler <richard@klingler.net>
2015-08-06 17:02 ` Re: IP address, subnet query behaves wrong for /32 Adrian Klaver <adrian.klaver@aklaver.com>
@ 2015-08-06 17:07 ` David G. Johnston <david.g.johnston@gmail.com>
2015-08-06 19:10 ` Re: IP address, subnet query behaves wrong for /32 Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 9+ messages in thread
From: David G. Johnston @ 2015-08-06 17:07 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: Richard RK. Klingler <richard@klingler.net>; pgsql-sql
On Thu, Aug 6, 2015 at 10:02 AM, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:
> 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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: IP address, subnet query behaves wrong for /32
2015-08-06 16:47 IP address, subnet query behaves wrong for /32 Richard RK. Klingler <richard@klingler.net>
2015-08-06 17:02 ` Re: IP address, subnet query behaves wrong for /32 Adrian Klaver <adrian.klaver@aklaver.com>
2015-08-06 17:07 ` Re: IP address, subnet query behaves wrong for /32 David G. Johnston <david.g.johnston@gmail.com>
@ 2015-08-06 19:10 ` Tom Lane <tgl@sss.pgh.pa.us>
2015-08-06 19:35 ` Re: IP address, subnet query behaves wrong for /32 Richard RK. Klingler <richard@klingler.net>
0 siblings, 1 reply; 9+ messages in thread
From: Tom Lane @ 2015-08-06 19:10 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Adrian Klaver <adrian.klaver@aklaver.com>; Richard RK. Klingler <richard@klingler.net>; pgsql-sql
"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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: IP address, subnet query behaves wrong for /32
2015-08-06 16:47 IP address, subnet query behaves wrong for /32 Richard RK. Klingler <richard@klingler.net>
2015-08-06 17:02 ` Re: IP address, subnet query behaves wrong for /32 Adrian Klaver <adrian.klaver@aklaver.com>
2015-08-06 17:07 ` Re: IP address, subnet query behaves wrong for /32 David G. Johnston <david.g.johnston@gmail.com>
2015-08-06 19:10 ` Re: IP address, subnet query behaves wrong for /32 Tom Lane <tgl@sss.pgh.pa.us>
@ 2015-08-06 19:35 ` Richard RK. Klingler <richard@klingler.net>
2015-08-06 19:45 ` Re: IP address, subnet query behaves wrong for /32 ktm@rice.edu <ktm@rice.edu>
2015-08-06 19:53 ` Re: IP address, subnet query behaves wrong for /32 Adrian Klaver <adrian.klaver@aklaver.com>
2015-08-17 14:30 ` Re: IP address, subnet query behaves wrong for /32 Peter Eisentraut <peter_e@gmx.net>
0 siblings, 3 replies; 9+ messages in thread
From: Richard RK. Klingler @ 2015-08-06 19:35 UTC (permalink / raw)
To: pgsql-sql
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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: IP address, subnet query behaves wrong for /32
2015-08-06 16:47 IP address, subnet query behaves wrong for /32 Richard RK. Klingler <richard@klingler.net>
2015-08-06 17:02 ` Re: IP address, subnet query behaves wrong for /32 Adrian Klaver <adrian.klaver@aklaver.com>
2015-08-06 17:07 ` Re: IP address, subnet query behaves wrong for /32 David G. Johnston <david.g.johnston@gmail.com>
2015-08-06 19:10 ` Re: IP address, subnet query behaves wrong for /32 Tom Lane <tgl@sss.pgh.pa.us>
2015-08-06 19:35 ` Re: IP address, subnet query behaves wrong for /32 Richard RK. Klingler <richard@klingler.net>
@ 2015-08-06 19:45 ` ktm@rice.edu <ktm@rice.edu>
2 siblings, 0 replies; 9+ messages in thread
From: ktm@rice.edu @ 2015-08-06 19:45 UTC (permalink / raw)
To: Richard RK. Klingler <richard@klingler.net>; +Cc: 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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: IP address, subnet query behaves wrong for /32
2015-08-06 16:47 IP address, subnet query behaves wrong for /32 Richard RK. Klingler <richard@klingler.net>
2015-08-06 17:02 ` Re: IP address, subnet query behaves wrong for /32 Adrian Klaver <adrian.klaver@aklaver.com>
2015-08-06 17:07 ` Re: IP address, subnet query behaves wrong for /32 David G. Johnston <david.g.johnston@gmail.com>
2015-08-06 19:10 ` Re: IP address, subnet query behaves wrong for /32 Tom Lane <tgl@sss.pgh.pa.us>
2015-08-06 19:35 ` Re: IP address, subnet query behaves wrong for /32 Richard RK. Klingler <richard@klingler.net>
@ 2015-08-06 19:53 ` Adrian Klaver <adrian.klaver@aklaver.com>
2 siblings, 0 replies; 9+ messages in thread
From: Adrian Klaver @ 2015-08-06 19:53 UTC (permalink / raw)
To: Richard RK. Klingler <richard@klingler.net>; pgsql-sql
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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: IP address, subnet query behaves wrong for /32
2015-08-06 16:47 IP address, subnet query behaves wrong for /32 Richard RK. Klingler <richard@klingler.net>
2015-08-06 17:02 ` Re: IP address, subnet query behaves wrong for /32 Adrian Klaver <adrian.klaver@aklaver.com>
2015-08-06 17:07 ` Re: IP address, subnet query behaves wrong for /32 David G. Johnston <david.g.johnston@gmail.com>
2015-08-06 19:10 ` Re: IP address, subnet query behaves wrong for /32 Tom Lane <tgl@sss.pgh.pa.us>
2015-08-06 19:35 ` Re: IP address, subnet query behaves wrong for /32 Richard RK. Klingler <richard@klingler.net>
@ 2015-08-17 14:30 ` Peter Eisentraut <peter_e@gmx.net>
2 siblings, 0 replies; 9+ messages in thread
From: Peter Eisentraut @ 2015-08-17 14:30 UTC (permalink / raw)
To: Richard RK. Klingler <richard@klingler.net>; pgsql-sql
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.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2015-08-17 14:30 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-08-06 16:47 IP address, subnet query behaves wrong for /32 Richard RK. Klingler <richard@klingler.net>
2015-08-06 17:01 ` David G. Johnston <david.g.johnston@gmail.com>
2015-08-06 17:02 ` Adrian Klaver <adrian.klaver@aklaver.com>
2015-08-06 17:07 ` David G. Johnston <david.g.johnston@gmail.com>
2015-08-06 19:10 ` Tom Lane <tgl@sss.pgh.pa.us>
2015-08-06 19:35 ` Richard RK. Klingler <richard@klingler.net>
2015-08-06 19:45 ` ktm@rice.edu <ktm@rice.edu>
2015-08-06 19:53 ` Adrian Klaver <adrian.klaver@aklaver.com>
2015-08-17 14:30 ` Peter Eisentraut <peter_e@gmx.net>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox