Re: IP address, subnet query behaves wrong for /32

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(at)postgresql(dot)org im Auftrag von Tom Lane" <[ADDRESS]>:
>
>> "David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
>>> On Thu, Aug 6, 2015 at 10:02 AM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)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(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-sql
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com