CODEWITHSUNDEEP
sqlpostgresqlip
Aley
Aley

Reputation: 8640

PostgreSQL field data type for IPv4 addresses

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!

Upvotes: 11

Views: 18042

Answers (3)

Craig Ringer
Craig Ringer

Reputation: 324375

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.

Upvotes: 19

Chris Travers
Chris Travers

Reputation: 26454

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.

Upvotes: 4

triclosan
triclosan

Reputation: 5714

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.

Upvotes: 4

Related Questions