Re: exclusion constraint for ranges of IP

Hi,<br />you can do the identification of customer by ip in many ways. IMHO, first of all, you have to put the allowed
IPsinto your table. <br />The simpler way is to write all IPs allowed, of course. <br />The simpler way to do range
checkis to have 2 columns in table, IP-range-starts and IP-range-ends, so the SQL could be easy using every data
types.<br/> I prefer to use string data type and not compound ip4r type because can check using reg.exp. in SQL select,
writingIPs range like 1.2.3.100-1.2.3.150 or just single 2.3.4.5. <br />When I did something like that, I wrote IP in
thisform : 001002003100 and I use the two columns of range-starts and range-ends, so it became very easy (and I think
veryperformant) doing check even with large table.<br /><br />The worse way is to remand check to PHP, because you have
toload entire table during select and manually select target row there.<br /><br />I hope these suggests help<br /><br
/><divclass="gmail_quote">2011/8/22 Herouth Maoz <span dir="ltr"><<a
href="mailto:herouth@unicell.co.il">herouth@unicell.co.il</a>></span><br/><blockquote class="gmail_quote"
style="margin:00 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;"><br /> On 22/08/2011, at 01:19, Harald Fuchs
wrote:<br/><br /> > In article <<a
href="mailto:CAF36091-203E-4C10-AA53-7D9087114D35@unicell.co.il">CAF36091-203E-4C10-AA53-7D9087114D35@unicell.co.il</a>>,<br
/>> Herouth Maoz <<a href="mailto:herouth@unicell.co.il">herouth@unicell.co.il</a>> writes:<br /> ><br />
>>Hi,<br /> >> 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
fallswithin the range, it is identified as a particular customer.<br /> ><br /> >> Naturally, I'd like to have
constraintson 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?<br /> ><br /> > This "new type" already exists: ip4r,
whichcan be found in pgfoundry.<br /> > With it you can do<br /> ><br /> > CREATE TABLE mytbl (<br /> >
 iprangeip4r NOT NULL,<br /> >  ...,<br /> >  CONSTRAINT range_check CHECK ((NOT overlap(iprange)))<br /> >
);<br/><br /><br /> Thank you.<br /><br /> I assume you can't use a CHECK constraint for between-rows constraints.
Wouldn'tthis  be<br /><br /> CONSTRAINT EXCLUDE ( iprange WITH && )<br /><br /> ?<br /><br /> 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
willhave to send and what it will receive when querying a compound type. I use PHP/ZF. I have just now posted a
questionon the pgsql-php list about this. I suspect I'll be getting a string which I'll have to parse, which would make
theapplication more complicated to read and understand.<br /><br /> Herouth<br /><font color="#888888">--<br /> Sent
viapgsql-sql mailing list (<a href="mailto:pgsql-sql@postgresql.org">pgsql-sql@postgresql.org</a>)<br /> To make
changesto your subscription:<br /><a href="http://www.postgresql.org/mailpref/pgsql-sql"
target="_blank">http://www.postgresql.org/mailpref/pgsql-sql</a><br/></font></blockquote></div><br /><br clear="all"
/><br/>-- <br />Simone<br />