Re: Dealing with unique IP adresses and ranges

Mark Fletcher <markf@wingedpig.com> writes:
> But what I want is to also be able to store an incomplete IP address,
> representing a range, say a class C block.

Are the ranges always standard ranges like /16, /18, /24, etc?  If so
you should really forget about both strings and integers and use the
CIDR datatype.

Unfortunately that only solves the data-representation issue and still
leaves you with an SQL issue.  You can easily test if there's overlap
between an existing entry and a proposed new one, say with
    new <<= old OR new >>= old
but since this doesn't correspond to a unique-index behavior you can't
easily get the system to enforce it for you.

The only way I can think of to solve it is to create a BEFORE INSERT
trigger that does the test the hard way:

  if exists(select 1 from mytab where ip >>= new.ip or ip <<= new.ip)
    then raise error;

and something a tad more complex for BEFORE UPDATE (you'd want to ignore
the row you're updating).  This is not going to scale nicely to huge
numbers of table entries, I fear, because of the non-indexability of
the queries; but it should work okay up to a few thousand entries.

It would be interesting to see whether a GIST operator class could be
devised that would support indexing <<= and >>= queries ... but I don't
suppose that's fit material for the NOVICE list ...

            regards, tom lane