Обсуждение: [PERFORM] Filter certain range of IP address.

On 2017-04-07 16:13, Dinesh Chandra 12108 wrote:
> Hi expert,
>
> May I know how to select a range of IP address.
>
> Example: I have number of different-2 IP's present in a table.
>
> I HAVE TO SELECT ONLY THAT IP ADDRESS WHICH DOES NOT START FROM PREFIX
> “172.23.110”.
>
> Thanks in advance
>
> REGARDS,
>
> DINESH CHANDRA
>
> |DATABASE ADMINISTRATOR (ORACLE/POSTGRESQL)| CYIENT LTD. NOIDA.
>
> ------------------------------------------------------------------
>
> Mobile: +91-9953975849 | Ext 1078 |dinesh.chandra@cyient.com
>
> Plot No. 7, NSEZ, Phase-II ,Noida-Dadri Road, Noida - 201 305,India.


If you store the ip address as the INET datatype then you can use the
INET operators
to see if any arbitraty number of bits match, the first 3 bytes means
the first 24 bits:


SELECT '172.23.110.55'::inet << '172.23.110.1/24'::inet;
  ?column?
----------
  t
(1 row)


SELECT '272.23.110.55'::inet << '172.23.110.1/24'::inet;
  ?column?
----------
  f
(1 row)

See also: https://www.postgresql.org/docs/9.3/static/functions-net.html

Dear Vinny,

Thanks for your valuable replay.

but I need a select query, which select only that record which starts from IP "172.23.110" only from below table.

xxx    172.23.110.175
yyy    172.23.110.178
zzz    172.23.110.177
aaa    172.23.110.176
bbb    172.23.111.180
ccc    172.23.115.26

Regards,
Dinesh Chandra
|Database administrator (Oracle/PostgreSQL)| Cyient Ltd. Noida.
------------------------------------------------------------------
Mobile: +91-9953975849 | Ext 1078 |dinesh.chandra@cyient.com 
Plot No. 7, NSEZ, Phase-II ,Noida-Dadri Road, Noida - 201 305,India.

-----Original Message-----
From: vinny [mailto:vinny@xs4all.nl] 
Sent: 07 April, 2017 7:52 PM
To: Dinesh Chandra 12108 <Dinesh.Chandra@cyient.com>
Cc: pgsql-performance-owner@postgresql.org; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Filter certain range of IP address.

On 2017-04-07 16:13, Dinesh Chandra 12108 wrote:
> Hi expert,
> 
> May I know how to select a range of IP address.
> 
> Example: I have number of different-2 IP's present in a table.
> 
> I HAVE TO SELECT ONLY THAT IP ADDRESS WHICH DOES NOT START FROM PREFIX 
> “172.23.110”.
> 
> Thanks in advance
> 
> REGARDS,
> 
> DINESH CHANDRA
> 
> |DATABASE ADMINISTRATOR (ORACLE/POSTGRESQL)| CYIENT LTD. NOIDA.
> 
> ------------------------------------------------------------------
> 
> Mobile: +91-9953975849 | Ext 1078 |dinesh.chandra@cyient.com
> 
> Plot No. 7, NSEZ, Phase-II ,Noida-Dadri Road, Noida - 201 305,India.


If you store the ip address as the INET datatype then you can use the INET operators to see if any arbitraty number of
bitsmatch, the first 3 bytes means the first 24 bits:
 


SELECT '172.23.110.55'::inet << '172.23.110.1/24'::inet;
  ?column?
----------
  t
(1 row)


SELECT '272.23.110.55'::inet << '172.23.110.1/24'::inet;
  ?column?
----------
  f
(1 row)

See also: https://www.postgresql.org/docs/9.3/static/functions-net.html

On Fri, Apr 7, 2017 at 11:29 AM, David G. Johnston <david.g.johnston@gmail.com> wrote:

On Fri, Apr 7, 2017 at 8:18 AM, Dinesh Chandra 12108 <Dinesh.Chandra@cyient.com> wrote:

Dear Vinny,

Thanks for your valuable replay.

but I need a select query, which select only that record which starts from IP "172.23.110" only from below table.

xxx     172.23.110.175
yyy     172.23.110.178
zzz     172.23.110.177
aaa     172.23.110.176
bbb     172.23.111.180
ccc     172.23.115.26

​SELECT ... WHERE substring(ip_addr::text, 1, 10) = '172.23.110'

or

   select ... where ip_addr << '172.23.110/32';

On 2017-04-07 17:29, David G. Johnston wrote:
> On Fri, Apr 7, 2017 at 8:18 AM, Dinesh Chandra 12108
> <Dinesh.Chandra@cyient.com> wrote:
>
>> Dear Vinny,
>>
>> Thanks for your valuable replay.
>>
>> but I need a select query, which select only that record which
>> starts from IP "172.23.110" only from below table.
>>
>> xxx     172.23.110.175
>> yyy     172.23.110.178
>> zzz     172.23.110.177
>> aaa     172.23.110.176
>> bbb     172.23.111.180
>> ccc     172.23.115.26
>
> ​SELECT ... WHERE substring(ip_addr::text, 1, 10) = '172.23.110'
>
> David J.
>  ​

While it's certainly possible to do it with a substring(), I'd strongly
advise against it,
for several reasons, but the main one is that it does not take into
account what happens to the presentation of the IP address when cast to
a string. There might be some conditions that cause it to render as
'172.023.110' instead of '172.23.110' just like numbers can be rendered
as '1.234,56' or '1,234.56' depending on locale, and that would break
the functionality without throwing an error.

Generally speaking; if you find yourself using a substring() on a
datatype other than a string,
you should check if there isn't an operator that already can do what you
want to do. PostgreSQL has operators
to do all the basic things with the datatypes it supports, so you don't
have to re-invent the wheel. :-)