Hi Herouth,
I think you are right about exclusion...
If you are getting 'string' I think then command would be:
INSERT INTO customer_ip_range(cutomer_id, ip4r) VALUES('customeridstring', ip4r('iprangestring'))
Kind Regards,
Misa
2011/8/22 Herouth Maoz
<herouth@unicell.co.il> 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, 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)))
> );
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