Re: exclusion constraint for ranges of IP

On 22/08/2011, at 01:19, Harald Fuchs wrote:

> In article <CAF36091-203E-4C10-AA53-7D9087114D35@unicell.co.il>,
> Herouth Maoz <herouth@unicell.co.il> writes:
>
>> Hi,
>> I'm designing a new database. One of the table contains allowed IP ranges for a customer (Fields: customer_id,
from_ip,to_ip) which is intended to check - if  an incoming connection's originating IP number falls within the range,
itis identified as a particular customer. 
>
>> Naturally, I'd like to have constraints on the table that prevent entering of ip ranges that overlap. Is there a way
todo that with exclusion constraints? Or do I have to define a new type for this? 
>
> This "new type" already exists: ip4r, which can be found in pgfoundry.
> With it you can do
>
> CREATE TABLE mytbl (
>  iprange ip4r NOT NULL,
>  ...,
>  CONSTRAINT range_check CHECK ((NOT overlap(iprange)))
> );


Thank you.

I assume you can't use a CHECK constraint for between-rows constraints. Wouldn't this  be

CONSTRAINT EXCLUDE ( iprange WITH && )

?

Basically, though, I'm not too happy about using compound types - that's why I asked if I have to. I'm not sure what my
applicationwill have to send and what it will receive when querying a compound type. I use PHP/ZF. I have just now
posteda question on the pgsql-php list about this. I suspect I'll be getting a string which I'll have to parse, which
wouldmake the application more complicated to read and understand. 

Herouth