PostgreSQL field data type for IPv4 addresses

Question

What is the correct data type for IPv4 addresses in PostgreSQL?

I heard about inet, but never used it.

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').

It would be nice to have the ability to SELECT the IP addresses by there subnet.

Thanks for any suggestions in advance!

Answer 1

http://www.postgresql.org/docs/current/static/datatype-net-types.html having field of inet type it is only what you need.

The operators <<, <<=, >>, and >>= test for subnet inclusion.

Answer 2

The built-in cidr and inet types will do what you want and provide suitable operators:

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.

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.

See also Chris's point re ip4r.

Answer 3

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/

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.