By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,609 Members | 3,812 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,609 IT Pros & Developers. It's quick & easy.

IP adrss and networks in Postgresql

P: n/a
I need to do some "IP address based guessing" what should happen inside my application.Ie if given ip adress is from network 'foo' do something, if given IP adrss is from network 'bar', do something else.
So I need to set up table with network definitions like this :

create table network_rules (network inet, rules text )

let's assume that I fill it with the definition of network or hosts.For example like this

10.0.0.1/24
192.168.0.1/24
192.168.0.128/25
192.168.0.200/32 (single host)

Now i have given *some* ip adress and I need to create a SELECT that return all networks with given ip adress belongs to , ordered by netmask, most accurate (ie more bits in netmask set ) first.

Is it possible with postgres and inet datatype ? I have no idea how to do that. Please help.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 22 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
"NTPT" <nt**@centrum.cz> writes:
I need to do some "IP address based guessing" what should happen inside my application.Ie if given ip adress is from network 'foo' do something, if given IP adrss is from network 'bar', do something else.
So I need to set up table with network definitions like this : create table network_rules (network inet, rules text )
Most likely you want to declare the network column as 'cidr' not 'inet',
so that the datatype will enforce that it's a valid network spec. I
can't imagine any case where you'd want bits set to the right of the
netmask in this table.
Now i have given *some* ip adress and I need to create a SELECT that return all networks with given ip adress belongs to , ordered by netmask, most accurate (ie more bits in netmask set ) first. Is it possible with postgres and inet datatype ?


Sure. The contains-or-equals operator selects the rows you want
(network >>= 'target-ip') and you can order by masklen(network).

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 22 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.