Subject: Re: IP address, subnet query behaves wrong for /32
To: "Richard RK. Klingler" <richard@klingler.net>,
 "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
References: <1FC8E571-8456-4085-B59B-016ECD365768@klingler.net>
 <55C39320.4010905@aklaver.com>
 <CAKFQuwaj9HFTAU-soNt3mn4rCTJ8FUgHtnTg+ZsV2oGmMT=n8w@mail.gmail.com>
 <16388.1438888231@sss.pgh.pa.us>
 <10B2FB45-DFE8-4075-BFB0-36023ADF9837@klingler.net>
From: Adrian Klaver <adrian.klaver@aklaver.com>
Message-ID: <55C3BB3D.2020103@aklaver.com>
Date: Thu, 6 Aug 2015 12:53:33 -0700
User-Agent: Mozilla/5.0 (X11; Linux i686; rv:38.0) Gecko/20100101
 Thunderbird/38.1.0
MIME-Version: 1.0
In-Reply-To: <10B2FB45-DFE8-4075-BFB0-36023ADF9837@klingler.net>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

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