Dealing with unique IP adresses and ranges

Поиск
Список
Период
Сортировка
От Mark Fletcher
Тема Dealing with unique IP adresses and ranges
Дата
Msg-id 3D7454FE.7010205@wingedpig.com
обсуждение исходный текст
Ответы Re: Dealing with unique IP adresses and ranges  (Oliver Elphick <olly@lfix.co.uk>)
Re: Dealing with unique IP adresses and ranges  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-novice
Hello,

No doubt this is a simple question for those more experienced with SQL
than I (that is, most everyone), but I'm stumped. For the table in
question, each row represents some information about a particular IP
address. IP addresses must be unique in regards to a particular user,
represented by a userId. So far, easy, and the following works for me
for this:

create unique index ip_index on ip_table( ip, userId );

But what I want is to also be able to store an incomplete IP address,
representing a range, say a class C block. And when I try to insert a
row representing a C block, if there are any rows that represent
complete IP addresses within that C block, it should return an error
(enforce uniqueness). And vice versa, if there's a row representing a
class C block, and I try to insert a complete IP address within that
block, it should return an error.

An example. I insert the following rows:

1.2.3.1
1.2.3.2
1.2.3.3

And I try to insert the IP address range 1.2.3, it should error out.
btw, in my app, IP addresses are represented not as strings, but as
ints. But if it makes things easier in the database, I can store them as
strings.

Hope this makes sense. How can I do this?

Thanks!


Mark



В списке pgsql-novice по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: formatting a date when some nulls exist
Следующее
От: Oliver Elphick
Дата:
Сообщение: Re: Dealing with unique IP adresses and ranges