Re: Dealing with unique IP adresses and ranges

On Tue, 2002-09-03 at 08:55, Mark Fletcher wrote:
> 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?
>

Sounds like you need a trigger to look for any tuples matching b << a.

> Thanks,
>
>
> Mark
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>
--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 E-Mail: ler(at)lerctr(dot)org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749