MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8;
 format=flowed
Content-Transfer-Encoding: 8bit
Date: Mon, 10 Apr 2017 13:33:05 +0200
From: vinny <vinny@xs4all.nl>
To: "David G. Johnston" <david.g.johnston@gmail.com>
Cc: Dinesh Chandra 12108 <Dinesh.Chandra@cyient.com>,
 pgsql-performance@postgresql.org
Subject: Re: Filter certain range of IP address.
In-Reply-To: 
 <CAKFQuwYVgCiUsDDHc9FjVCtsDttZHNjHnukWwFz3TFtZwAj=2Q@mail.gmail.com>
References: <cfb55df0df5547ccacef2b7390381bb3@cyient.com>
 <9deae42ab489b5856f600ea514bcd51a@xs4all.nl>
 <705a033c9efe4a75830cc76b154e18a0@cyient.com>
 <CAKFQuwYVgCiUsDDHc9FjVCtsDttZHNjHnukWwFz3TFtZwAj=2Q@mail.gmail.com>
Message-ID: <d5ba4c075cf856aa6322a933451c162a@xs4all.nl>
User-Agent: XS4ALL Webmail
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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. :-)


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance