Thread: Data types for IP address.

a regular varchar or text field.

On 02/23/2011 02:09 PM, Gaini Rajeshwar wrote:

Hi All,

I wanted to store ip addresses in table. I wanted to support the following 3 types of ip addresses.

1. Wildcard format     :     1.2.3.*



2. CIDR format            :     1.2.3/24  OR  1.2.3.4/255.255.255.0



3. Start-End IP format :      1.2.3.0-1.2.3.255






I had a look at CIDR datatype and inet datatype but it seems they support only partial of the above requirements. Any suggestions on what datatype is useful for this purpose?

Thanks

On Wed, Feb 23, 2011 at 02:30:18PM +0200,
 Sim Zacks <sim@compulab.co.il> wrote
 a message of 97 lines which said:

> a regular varchar or text field.

Very bad idea since they don't support canonicalization (2001:db8::1
== 2001:db8:0:0:0:0:0:1) or masking (set_masklen(address, 20)).

On Wed, Feb 23, 2011 at 05:39:26PM +0530,
 Gaini Rajeshwar <raja.rajeshwar2006@gmail.com> wrote
 a message of 52 lines which said:

> I wanted to store ip addresses in table. I wanted to support the following 3
> types of ip addresses.
>
> *1. Wildcard format     :*     1.2.3.*
> *
> *
> *2. CIDR format            :*     1.2.3/24  OR  1.2.3.4/255.255.255.0

What is the difference between 1.2.3.* and 1.2.3.0/24? For me, it is
exactly the same. If you just want the ability to INPUT 1.2.3.*, just
write a small conversion routine in your favorite programming
language.

> *3. Start-End IP format :*      1.2.3.0-1.2.3.255

You don't even need to program the conversion, it is already done:

% netmask 1.2.3.0:1.2.3.255
        1.2.3.0/24

On 02/23/11 4:44 AM, Stephane Bortzmeyer wrote:
>> *3. Start-End IP format :*      1.2.3.0-1.2.3.255
> You don't even need to program the conversion, it is already done:
>
> % netmask 1.2.3.0:1.2.3.255
>          1.2.3.0/24

yes, but what about 10.1.2.57-10.1.2.123 ?    presumably valid in his
range system, and certainly NOT a valid CIDR range.

On Feb 23, 2011, at 13:49, John R Pierce wrote:

> On 02/23/11 4:44 AM, Stephane Bortzmeyer wrote:
>>> *3. Start-End IP format :*      1.2.3.0-1.2.3.255
>> You don't even need to program the conversion, it is already done:
>>
>> % netmask 1.2.3.0:1.2.3.255
>>         1.2.3.0/24
>
> yes, but what about 10.1.2.57-10.1.2.123 ?    presumably valid in his range system, and certainly NOT a valid CIDR
range.

If it hasn't been mentioned already, take a look at ip4r. <http://pgfoundry.org/projects/ip4r/>

Michael Glaesemann
grzm seespotcode net

John R Pierce <pierce@hogranch.com> writes:
> On 02/23/11 4:44 AM, Stephane Bortzmeyer wrote:
> *3. Start-End IP format :*      1.2.3.0-1.2.3.255
>> You don't even need to program the conversion, it is already done:
>>
>> % netmask 1.2.3.0:1.2.3.255
>> 1.2.3.0/24

> yes, but what about 10.1.2.57-10.1.2.123 ?    presumably valid in his
> range system, and certainly NOT a valid CIDR range.

The question is does he actually have a use-case for address ranges that
don't correspond to legal CIDR ranges, but do nonetheless have an
identifiable lower boundary, upper boundary, and no holes?  And if so,
what is it?  The whole thing looked to me like somebody inventing
requirements with little or no study of what they really needed.

            regards, tom lane

On 02/23/11 1:33 PM, Tom Lane wrote:
> The question is does he actually have a use-case for address ranges that
> don't correspond to legal CIDR ranges, but do nonetheless have an
> identifiable lower boundary, upper boundary, and no holes?  And if so,
> what is it?  The whole thing looked to me like somebody inventing
> requirements with little or no study of what they really needed.

indeed.    i had to poke around the ban tables of MyBB the other day (a
php bbs that works reasonably well* on postgresql), and they were
storing IP bans as strings like 1.2.*.* or 1.2.3.4-1.2.4.5 or whatever.
   UGH.




* it works mostly on PG but has some bugs around the edges of the admin
stuff. the worst ones I've found fixes for and submitted back to the
mybb team, but mostly I'm too lazy and just work around them