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

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.