Re: exclusion constraint for ranges of IP

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

> In article <CAF36091-203E-4C10-AA53-7D9087114D35(at)unicell(dot)co(dot)il>,
> Herouth Maoz <herouth(at)unicell(dot)co(dot)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, it is 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 to do 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 application will have to send and what it will receive when querying a compound type. I use PHP/ZF. I have just now posted a question on the pgsql-php list about this. I suspect I'll be getting a string which I'll have to parse, which would make the application more complicated to read and understand.

Herouth