Re: Dealing with unique IP adresses and ranges

Oliver Elphick wrote:

>On Tue, 2002-09-03 at 07:21, Mark Fletcher wrote:
>
>
>>Hello,
>>
>>No doubt this is a simple question for those more experienced with SQL
>>than I (that is, most everyone), but I'm stumped. For the table in
>>question, each row represents some information about a particular IP
>>address. IP addresses must be unique in regards to a particular user,
>>represented by a userId. So far, easy, and the following works for me
>>for this:
>>
>>create unique index ip_index on ip_table( ip, userId );
>>
>>But what I want is to also be able to store an incomplete IP address,
>>representing a range, say a class C block. And when I try to insert a
>>row representing a C block, if there are any rows that represent
>>complete IP addresses within that C block, it should return an error
>>(enforce uniqueness). And vice versa, if there's a row representing a
>>class C block, and I try to insert a complete IP address within that
>>block, it should return an error.
>>
>>An example. I insert the following rows:
>>
>>1.2.3.1
>>1.2.3.2
>>1.2.3.3
>>
>>And I try to insert the IP address range 1.2.3, it should error out.
>>btw, in my app, IP addresses are represented not as strings, but as
>>ints. But if it makes things easier in the database, I can store them as
>>strings.
>>
>>Hope this makes sense. How can I do this?
>>
>>
>
>Have you thought about using the inet or cidr datatypes, rather than
>string or int?
>
>You could index on network(ip)
>
>
>
Thanks for the reply. I've gone through the docs for network datatypes
and functions. The operator '<<' does the test I think I'm looking for
(b is contained in a). But how do I incorporate that into a constraint
on the table?

Thanks,

Mark