Inet
| Name | Aliases | Description |
|---|---|---|
INET | IPv4 or IPv6 host address with an optional netmask | |
CIDR | IPv4 or IPv6 network address |
An INET value holds an address and a netmask length. Without a netmask the value is a single host: /32 for IPv4, /128 for IPv6. With a netmask it can describe a network, and it may keep host bits beyond the netmask, as in 192.168.1.5/24.
SELECT '192.168.1.5/24'::INET AS v4, '2001:db8::1'::INET AS v6;v4 | v6 ----------------+------------- 192.168.1.5/24 | 2001:db8::1
Ordering
INET values sort the way PostgreSQL sorts them: IPv4 before IPv6, then by the network part (the address with the bits beyond the netmask cleared), then by netmask length, then by the full address. So a value sorts next to the network it belongs to, and a wider network sorts before a narrower one with the same network part.
The same order applies to <, <=, >, >=, BETWEEN, ORDER BY, min, max, arg_min, arg_max, list_min, list_max, greatest, least, window functions and ANY/ALL subqueries, and to arrays and rows that contain INET values. Equality compares the address and the netmask length, so 10.0.0.1/8 and 10.0.0.2/8 are different values.
SELECT x FROM (VALUES ('10.0.0.1'::INET), ('10.0.0.5/8'::INET), ('::1'::INET), ('10.0.0.0/16'::INET)) t(x) ORDER BY x;x ------------- 10.0.0.5/8 10.0.0.0/16 10.0.0.1 ::1
Containment
a <<= b is true when a lies within the network b or equals it, and a >>= b is true when a contains b or equals it. An IPv4 value is never contained in an IPv6 network, nor the other way around.
SELECT '10.1.2.3'::INET <<= '10.0.0.0/8'::INET AS inside, '10.0.0.0/8'::INET >>= '10.1.2.3'::INET AS contains, '1.2.3.4'::INET <<= '::/0'::INET AS other_family;inside | contains | other_family --------+----------+-------------- t | t | f
a << b and a >> b are the strict forms: they are false when a and b have the same network and netmask. a && b is true when either network contains the other or they are equal.
SELECT '10.1.0.0/16'::INET << '10.0.0.0/8'::INET AS inside, '10.0.0.0/8'::INET << '10.0.0.0/8'::INET AS same_network, '10.0.0.0/8'::INET >> '10.1.2.3'::INET AS contains, '10.0.0.0/8'::INET && '10.1.0.0/16'::INET AS overlaps, '10.0.0.0/8'::INET && '11.0.0.0/8'::INET AS disjoint;inside | same_network | contains | overlaps | disjoint --------+--------------+----------+----------+---------- t | f | t | t | f
Bitwise operators
~ a inverts every bit of the address and keeps the netmask. a & b and a | b combine the two addresses bit by bit and take the longer of the two netmasks; both values must be of the same family, otherwise the result is an error. All three return INET, also for CIDR arguments.
SELECT ~ '10.1.2.3/24'::INET AS inverted, '10.1.2.3/24'::INET & '0.0.255.255/8'::INET AS anded, '10.1.2.3/8'::INET | '0.0.0.255/24'::INET AS ored;
SELECT '10.1.2.3'::INET & '::1'::INET;inverted | anded | ored --------------------+------------+--------------- 245.254.253.252/24 | 0.0.2.3/24 | 10.1.2.255/24 db error: ERROR: cannot AND inet values of different sizes
Indexing
An inverted index over an INET column answers equality, IN, comparisons, BETWEEN, <<=, >>=, <<, >> and &&. Index the column without a text search dictionary. An expression that returns INET, such as network(ip), can be indexed and searched the same way.
CREATE INDEX hosts_idx ON hosts USING inverted (id, ip);
VACUUM (REFRESH_TABLE) hosts;
SELECT id, ip FROM hosts_idx WHERE ip <<= '10.0.0.0/8' ORDER BY id;id | ip ----+------------ 1 | 10.1.2.3 2 | 10.200.0.7
>>= finds the stored networks that contain an address, which suits tables of networks such as routing or geolocation tables.
CREATE INDEX networks_idx ON networks USING inverted (id, net) INCLUDE (name);
VACUUM (REFRESH_TABLE) networks;
SELECT id, name FROM networks_idx WHERE net >>= '10.1.2.3' ORDER BY id;id | name ----+----------- 1 | corporate 2 | office
Conversion
A string converts to INET when it is a valid address, as in PostgreSQL, and so does a JSON or VARIANT string. A STRUCT, row, JSON object or VARIANT object converts when its fields ip_type (1 for IPv4, 2 for IPv6), address and mask form a valid address. address is the value as stored, which for IPv6 has its top bit flipped, so an INET converted to such a STRUCT converts back unchanged. If one of these fields is NULL, the result is NULL. Any other invalid value is an error; TRY_CAST returns NULL instead.
to_json, row_to_json, json_build_object and a cast to JSON write an INET or CIDR value as a JSON string of its text, as in PostgreSQL.
SELECT '{"ip_type": 1, "address": 167772161, "mask": 8}'::JSON::INET AS valid, '{"ip_type": 1, "address": null, "mask": 8}'::JSON::INET AS null_field, TRY_CAST('{"ip_type": 7, "address": 1, "mask": 8}'::JSON AS INET) AS try_invalid, to_json('2001:db8::1/64'::INET) AS json, '"10.0.0.0/8"'::JSON::INET AS json_string;
SELECT '{"ip_type": 7, "address": 1, "mask": 8}'::JSON::INET;valid | null_field | try_invalid | json | json_string ------------+------------+-------------+------------------+------------- 10.0.0.1/8 | NULL | NULL | "2001:db8::1/64" | 10.0.0.0/8 db error: ERROR: Invalid INET value: ip_type must be 1 (IPv4) or 2 (IPv6)
CIDR
A CIDR value is a network: an INET whose bits beyond the netmask are all zero. A string with host bits set, such as '192.168.1.5/24', is an error, and a CIDR value always shows its netmask, even /32 and /128. Converting an INET to CIDR clears the host bits, network returns a CIDR, and a CIDR value is used as an INET wherever one is expected. Mixing the two, as in COALESCE or UNION, gives INET. CIDR columns sort, compare and are indexed the same way as INET columns.
SELECT '10.1.2.3'::CIDR AS host, '10.0.0.0/8'::CIDR AS net, '192.168.1.5/24'::INET::CIDR AS from_inet, network('10.1.2.3/16'::INET) AS network;
SELECT '192.168.1.5/24'::CIDR;host | net | from_inet | network -------------+------------+----------------+------------- 10.1.2.3/32 | 10.0.0.0/8 | 192.168.1.0/24 | 10.1.0.0/16 db error: ERROR: invalid cidr value: "192.168.1.5/24"
Unlike PostgreSQL, abbreviated input such as '10' for 10.0.0.0/8 is not accepted, and min and max of a CIDR column return a CIDR value rather than an INET one.
Functions
host, network, broadcast, netmask and family take an INET value. Adding an integer to an INET value, or subtracting one, moves the address.
inet_sort_key(ip) returns a BLOB that compares byte by byte in the order described in Ordering. Use it to carry the INET order into a context that compares bytes, such as a key built for another system.
SELECT host('192.168.1.5/24'::INET) AS host, network('192.168.1.5/24'::INET) AS network, broadcast('192.168.1.5/24'::INET) AS broadcast, family('::1'::INET) AS family;host | network | broadcast | family -------------+----------------+------------------+-------- 192.168.1.5 | 192.168.1.0/24 | 192.168.1.255/24 | 6