From: vinny <vinny@xs4all.nl>
To: David G. Johnston <david.g.johnston@gmail.com>
Cc: Dinesh Chandra 12108 <Dinesh.Chandra@cyient.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Filter certain range of IP address.
Date: Mon, 10 Apr 2017 13:33:05 +0200
Message-ID: <d5ba4c075cf856aa6322a933451c162a@xs4all.nl> (raw)
In-Reply-To: <CAKFQuwYVgCiUsDDHc9FjVCtsDttZHNjHnukWwFz3TFtZwAj=2Q@mail.gmail.com>
References: <cfb55df0df5547ccacef2b7390381bb3@cyient.com>
<9deae42ab489b5856f600ea514bcd51a@xs4all.nl>
<705a033c9efe4a75830cc76b154e18a0@cyient.com>
<CAKFQuwYVgCiUsDDHc9FjVCtsDttZHNjHnukWwFz3TFtZwAj=2Q@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: vinny@xs4all.nl, david.g.johnston@gmail.com, Dinesh.Chandra@cyient.com
Subject: Re: Filter certain range of IP address.
In-Reply-To: <d5ba4c075cf856aa6322a933451c162a@xs4all.nl>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox