What is the correct data type for IPv4 addresses in PostgreSQL?
PostgreSQL中IPv4地址的正确数据类型是什么?
I heard about inet, but never used it.
我听说过inet,但从未使用它。
I need to perform SELECT queries like SELECT ... WHERE ip = '99.88.99.88' and it should support the output of human readable format (by human readable I mean '99.88.99.88').
我需要执行SELECT查询,如SELECT ... WHERE ip = '99 .88.99.88',它应该支持人类可读格式的输出(通过人类可读,我的意思是'99 .88.99.88')。
It would be nice to have the ability to SELECT the IP addresses by there subnet.
能够通过子网选择IP地址会很高兴。
Thanks for any suggestions in advance!
感谢您的任何建议!
3 个解决方案
#1
15
The built-in cidr and inet types will do what you want and provide suitable operators:
内置的cidr和inet类型将满足您的需求并提供合适的运算符:
regress=> SELECT '192.168.1.19'::inet << '192.168.1.0/24'::cidr;
?column?
----------
t
(1 row)
See the PostgreSQL documentation on network datatype operators and functions and on the network datatypes.
请参阅有关网络数据类型运算符和函数以及网络数据类型的PostgreSQL文档。
Limited index support is provided for the cidr and inet types; in particular, 'address in range' type queries are turned into range queries at least where the address is a constant. See this (rather old) thread.
为cidr和inet类型提供了有限的索引支持;特别是,“范围内地址”类型的查询至少在地址为常量的情况下变为范围查询。看到这个(相当古老的)线程。
See also Chris's point re ip4r.
另见Chris的观点re ip4r。
#2
4
I want to add that the built-in types are quite powerful, and that's where you should start. However if you need GIN indexing, check out http://pgfoundry.org/projects/ip4r/
我想补充一点,内置类型非常强大,这就是你应该开始的地方。但是,如果您需要GIN索引,请查看http://pgfoundry.org/projects/ip4r/
A case for ip4r (why I started using it) might be if you need to store CIDR blocks and make sure that no block contains any other block. Since this requires GIN indexing, you have to go with ip4r rather than the built-in types.
ip4r的一个案例(为什么我开始使用它)可能是你需要存储CIDR块并确保没有块包含任何其他块。由于这需要GIN索引,因此必须使用ip4r而不是内置类型。
#3
3
http://www.postgresql.org/docs/current/static/datatype-net-types.html having field of inet type it is only what you need.
http://www.postgresql.org/docs/current/static/datatype-net-types.html具有inet类型的字段,它只是你需要的。
The operators <<, <<=, >>, and >>= test for subnet inclusion.
运算符<<,<< =,>>和>> =测试子网包含。
#1
15
The built-in cidr and inet types will do what you want and provide suitable operators:
内置的cidr和inet类型将满足您的需求并提供合适的运算符:
regress=> SELECT '192.168.1.19'::inet << '192.168.1.0/24'::cidr;
?column?
----------
t
(1 row)
See the PostgreSQL documentation on network datatype operators and functions and on the network datatypes.
请参阅有关网络数据类型运算符和函数以及网络数据类型的PostgreSQL文档。
Limited index support is provided for the cidr and inet types; in particular, 'address in range' type queries are turned into range queries at least where the address is a constant. See this (rather old) thread.
为cidr和inet类型提供了有限的索引支持;特别是,“范围内地址”类型的查询至少在地址为常量的情况下变为范围查询。看到这个(相当古老的)线程。
See also Chris's point re ip4r.
另见Chris的观点re ip4r。
#2
4
I want to add that the built-in types are quite powerful, and that's where you should start. However if you need GIN indexing, check out http://pgfoundry.org/projects/ip4r/
我想补充一点,内置类型非常强大,这就是你应该开始的地方。但是,如果您需要GIN索引,请查看http://pgfoundry.org/projects/ip4r/
A case for ip4r (why I started using it) might be if you need to store CIDR blocks and make sure that no block contains any other block. Since this requires GIN indexing, you have to go with ip4r rather than the built-in types.
ip4r的一个案例(为什么我开始使用它)可能是你需要存储CIDR块并确保没有块包含任何其他块。由于这需要GIN索引,因此必须使用ip4r而不是内置类型。
#3
3
http://www.postgresql.org/docs/current/static/datatype-net-types.html having field of inet type it is only what you need.
http://www.postgresql.org/docs/current/static/datatype-net-types.html具有inet类型的字段,它只是你需要的。
The operators <<, <<=, >>, and >>= test for subnet inclusion.
运算符<<,<< =,>>和>> =测试子网包含。