<?xml version="1.0" encoding="UTF-8" standalone="no"?> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>9.12. Network Address Functions and Operators</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="functions-geometry.html" title="9.11. Geometric Functions and Operators" /><link rel="next" href="functions-textsearch.html" title="9.13. Text Search Functions and Operators" /></head><body><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">9.12. Network Address Functions and Operators</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="functions-geometry.html" title="9.11. Geometric Functions and Operators">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="functions.html" title="Chapter 9. Functions and Operators">Up</a></td><th width="60%" align="center">Chapter 9. Functions and Operators</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 11.7 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="functions-textsearch.html" title="9.13. Text Search Functions and Operators">Next</a></td></tr></table><hr></hr></div><div class="sect1" id="FUNCTIONS-NET"><div class="titlepage"><div><div><h2 class="title" style="clear: both">9.12. Network Address Functions and Operators</h2></div></div></div><p> <a class="xref" href="functions-net.html#CIDR-INET-OPERATORS-TABLE" title="Table 9.36. cidr and inet Operators">Table 9.36</a> shows the operators available for the <code class="type">cidr</code> and <code class="type">inet</code> types. The operators <code class="literal"><<</code>, <code class="literal"><<=</code>, <code class="literal">>></code>, <code class="literal">>>=</code>, and <code class="literal">&&</code> test for subnet inclusion. They consider only the network parts of the two addresses (ignoring any host part) and determine whether one network is identical to or a subnet of the other. </p><div class="table" id="CIDR-INET-OPERATORS-TABLE"><p class="title"><strong>Table 9.36. <code class="type">cidr</code> and <code class="type">inet</code> Operators</strong></p><div class="table-contents"><table class="table" summary="cidr and inet Operators" border="1"><colgroup><col /><col /><col /></colgroup><thead><tr><th>Operator</th><th>Description</th><th>Example</th></tr></thead><tbody><tr><td> <code class="literal"><</code> </td><td>is less than</td><td><code class="literal">inet '192.168.1.5' < inet '192.168.1.6'</code></td></tr><tr><td> <code class="literal"><=</code> </td><td>is less than or equal</td><td><code class="literal">inet '192.168.1.5' <= inet '192.168.1.5'</code></td></tr><tr><td> <code class="literal">=</code> </td><td>equals</td><td><code class="literal">inet '192.168.1.5' = inet '192.168.1.5'</code></td></tr><tr><td> <code class="literal">>=</code> </td><td>is greater or equal</td><td><code class="literal">inet '192.168.1.5' >= inet '192.168.1.5'</code></td></tr><tr><td> <code class="literal">></code> </td><td>is greater than</td><td><code class="literal">inet '192.168.1.5' > inet '192.168.1.4'</code></td></tr><tr><td> <code class="literal"><></code> </td><td>is not equal</td><td><code class="literal">inet '192.168.1.5' <> inet '192.168.1.4'</code></td></tr><tr><td> <code class="literal"><<</code> </td><td>is contained by</td><td><code class="literal">inet '192.168.1.5' << inet '192.168.1/24'</code></td></tr><tr><td> <code class="literal"><<=</code> </td><td>is contained by or equals</td><td><code class="literal">inet '192.168.1/24' <<= inet '192.168.1/24'</code></td></tr><tr><td> <code class="literal">>></code> </td><td>contains</td><td><code class="literal">inet '192.168.1/24' >> inet '192.168.1.5'</code></td></tr><tr><td> <code class="literal">>>=</code> </td><td>contains or equals</td><td><code class="literal">inet '192.168.1/24' >>= inet '192.168.1/24'</code></td></tr><tr><td> <code class="literal">&&</code> </td><td>contains or is contained by</td><td><code class="literal">inet '192.168.1/24' && inet '192.168.1.80/28'</code></td></tr><tr><td> <code class="literal">~</code> </td><td>bitwise NOT</td><td><code class="literal">~ inet '192.168.1.6'</code></td></tr><tr><td> <code class="literal">&</code> </td><td>bitwise AND</td><td><code class="literal">inet '192.168.1.6' & inet '0.0.0.255'</code></td></tr><tr><td> <code class="literal">|</code> </td><td>bitwise OR</td><td><code class="literal">inet '192.168.1.6' | inet '0.0.0.255'</code></td></tr><tr><td> <code class="literal">+</code> </td><td>addition</td><td><code class="literal">inet '192.168.1.6' + 25</code></td></tr><tr><td> <code class="literal">-</code> </td><td>subtraction</td><td><code class="literal">inet '192.168.1.43' - 36</code></td></tr><tr><td> <code class="literal">-</code> </td><td>subtraction</td><td><code class="literal">inet '192.168.1.43' - inet '192.168.1.19'</code></td></tr></tbody></table></div></div><br class="table-break" /><p> <a class="xref" href="functions-net.html#CIDR-INET-FUNCTIONS-TABLE" title="Table 9.37. cidr and inet Functions">Table 9.37</a> shows the functions available for use with the <code class="type">cidr</code> and <code class="type">inet</code> types. The <code class="function">abbrev</code>, <code class="function">host</code>, and <code class="function">text</code> functions are primarily intended to offer alternative display formats. </p><div class="table" id="CIDR-INET-FUNCTIONS-TABLE"><p class="title"><strong>Table 9.37. <code class="type">cidr</code> and <code class="type">inet</code> Functions</strong></p><div class="table-contents"><table class="table" summary="cidr and inet Functions" border="1"><colgroup><col /><col /><col /><col /><col /></colgroup><thead><tr><th>Function</th><th>Return Type</th><th>Description</th><th>Example</th><th>Result</th></tr></thead><tbody><tr><td> <a id="id-1.5.8.17.5.2.2.1.1.1" class="indexterm"></a> <code class="literal"><code class="function">abbrev(<code class="type">inet</code>)</code></code> </td><td><code class="type">text</code></td><td>abbreviated display format as text</td><td><code class="literal">abbrev(inet '10.1.0.0/16')</code></td><td><code class="literal">10.1.0.0/16</code></td></tr><tr><td><code class="literal"><code class="function">abbrev(<code class="type">cidr</code>)</code></code></td><td><code class="type">text</code></td><td>abbreviated display format as text</td><td><code class="literal">abbrev(cidr '10.1.0.0/16')</code></td><td><code class="literal">10.1/16</code></td></tr><tr><td> <a id="id-1.5.8.17.5.2.2.3.1.1" class="indexterm"></a> <code class="literal"><code class="function">broadcast(<code class="type">inet</code>)</code></code> </td><td><code class="type">inet</code></td><td>broadcast address for network</td><td><code class="literal">broadcast('192.168.1.5/24')</code></td><td><code class="literal">192.168.1.255/24</code></td></tr><tr><td> <a id="id-1.5.8.17.5.2.2.4.1.1" class="indexterm"></a> <code class="literal"><code class="function">family(<code class="type">inet</code>)</code></code> </td><td><code class="type">int</code></td><td>extract family of address; <code class="literal">4</code> for IPv4, <code class="literal">6</code> for IPv6</td><td><code class="literal">family('::1')</code></td><td><code class="literal">6</code></td></tr><tr><td> <a id="id-1.5.8.17.5.2.2.5.1.1" class="indexterm"></a> <code class="literal"><code class="function">host(<code class="type">inet</code>)</code></code> </td><td><code class="type">text</code></td><td>extract IP address as text</td><td><code class="literal">host('192.168.1.5/24')</code></td><td><code class="literal">192.168.1.5</code></td></tr><tr><td> <a id="id-1.5.8.17.5.2.2.6.1.1" class="indexterm"></a> <code class="literal"><code class="function">hostmask(<code class="type">inet</code>)</code></code> </td><td><code class="type">inet</code></td><td>construct host mask for network</td><td><code class="literal">hostmask('192.168.23.20/30')</code></td><td><code class="literal">0.0.0.3</code></td></tr><tr><td> <a id="id-1.5.8.17.5.2.2.7.1.1" class="indexterm"></a> <code class="literal"><code class="function">masklen(<code class="type">inet</code>)</code></code> </td><td><code class="type">int</code></td><td>extract netmask length</td><td><code class="literal">masklen('192.168.1.5/24')</code></td><td><code class="literal">24</code></td></tr><tr><td> <a id="id-1.5.8.17.5.2.2.8.1.1" class="indexterm"></a> <code class="literal"><code class="function">netmask(<code class="type">inet</code>)</code></code> </td><td><code class="type">inet</code></td><td>construct netmask for network</td><td><code class="literal">netmask('192.168.1.5/24')</code></td><td><code class="literal">255.255.255.0</code></td></tr><tr><td> <a id="id-1.5.8.17.5.2.2.9.1.1" class="indexterm"></a> <code class="literal"><code class="function">network(<code class="type">inet</code>)</code></code> </td><td><code class="type">cidr</code></td><td>extract network part of address</td><td><code class="literal">network('192.168.1.5/24')</code></td><td><code class="literal">192.168.1.0/24</code></td></tr><tr><td> <a id="id-1.5.8.17.5.2.2.10.1.1" class="indexterm"></a> <code class="literal"><code class="function">set_masklen(<code class="type">inet</code>, <code class="type">int</code>)</code></code> </td><td><code class="type">inet</code></td><td>set netmask length for <code class="type">inet</code> value</td><td><code class="literal">set_masklen('192.168.1.5/24', 16)</code></td><td><code class="literal">192.168.1.5/16</code></td></tr><tr><td><code class="literal"><code class="function">set_masklen(<code class="type">cidr</code>, <code class="type">int</code>)</code></code></td><td><code class="type">cidr</code></td><td>set netmask length for <code class="type">cidr</code> value</td><td><code class="literal">set_masklen('192.168.1.0/24'::cidr, 16)</code></td><td><code class="literal">192.168.0.0/16</code></td></tr><tr><td> <a id="id-1.5.8.17.5.2.2.12.1.1" class="indexterm"></a> <code class="literal"><code class="function">text(<code class="type">inet</code>)</code></code> </td><td><code class="type">text</code></td><td>extract IP address and netmask length as text</td><td><code class="literal">text(inet '192.168.1.5')</code></td><td><code class="literal">192.168.1.5/32</code></td></tr><tr><td> <a id="id-1.5.8.17.5.2.2.13.1.1" class="indexterm"></a> <code class="literal"><code class="function">inet_same_family(<code class="type">inet</code>, <code class="type">inet</code>)</code></code> </td><td><code class="type">boolean</code></td><td>are the addresses from the same family?</td><td><code class="literal">inet_same_family('192.168.1.5/24', '::1')</code></td><td><code class="literal">false</code></td></tr><tr><td> <a id="id-1.5.8.17.5.2.2.14.1.1" class="indexterm"></a> <code class="literal"><code class="function">inet_merge(<code class="type">inet</code>, <code class="type">inet</code>)</code></code> </td><td><code class="type">cidr</code></td><td>the smallest network which includes both of the given networks</td><td><code class="literal">inet_merge('192.168.1.5/24', '192.168.2.5/24')</code></td><td><code class="literal">192.168.0.0/22</code></td></tr></tbody></table></div></div><br class="table-break" /><p> Any <code class="type">cidr</code> value can be cast to <code class="type">inet</code> implicitly or explicitly; therefore, the functions shown above as operating on <code class="type">inet</code> also work on <code class="type">cidr</code> values. (Where there are separate functions for <code class="type">inet</code> and <code class="type">cidr</code>, it is because the behavior should be different for the two cases.) Also, it is permitted to cast an <code class="type">inet</code> value to <code class="type">cidr</code>. When this is done, any bits to the right of the netmask are silently zeroed to create a valid <code class="type">cidr</code> value. In addition, you can cast a text value to <code class="type">inet</code> or <code class="type">cidr</code> using normal casting syntax: for example, <code class="literal">inet(<em class="replaceable"><code>expression</code></em>)</code> or <code class="literal"><em class="replaceable"><code>colname</code></em>::cidr</code>. </p><p> <a class="xref" href="functions-net.html#MACADDR-FUNCTIONS-TABLE" title="Table 9.38. macaddr Functions">Table 9.38</a> shows the functions available for use with the <code class="type">macaddr</code> type. The function <code class="literal"><code class="function">trunc(<code class="type">macaddr</code>)</code></code> returns a MAC address with the last 3 bytes set to zero. This can be used to associate the remaining prefix with a manufacturer. </p><div class="table" id="MACADDR-FUNCTIONS-TABLE"><p class="title"><strong>Table 9.38. <code class="type">macaddr</code> Functions</strong></p><div class="table-contents"><table class="table" summary="macaddr Functions" border="1"><colgroup><col /><col /><col /><col /><col /></colgroup><thead><tr><th>Function</th><th>Return Type</th><th>Description</th><th>Example</th><th>Result</th></tr></thead><tbody><tr><td> <a id="id-1.5.8.17.8.2.2.1.1.1" class="indexterm"></a> <code class="literal"><code class="function">trunc(<code class="type">macaddr</code>)</code></code> </td><td><code class="type">macaddr</code></td><td>set last 3 bytes to zero</td><td><code class="literal">trunc(macaddr '12:34:56:78:90:ab')</code></td><td><code class="literal">12:34:56:00:00:00</code></td></tr></tbody></table></div></div><br class="table-break" /><p> The <code class="type">macaddr</code> type also supports the standard relational operators (<code class="literal">></code>, <code class="literal"><=</code>, etc.) for lexicographical ordering, and the bitwise arithmetic operators (<code class="literal">~</code>, <code class="literal">&</code> and <code class="literal">|</code>) for NOT, AND and OR. </p><p> <a class="xref" href="functions-net.html#MACADDR8-FUNCTIONS-TABLE" title="Table 9.39. macaddr8 Functions">Table 9.39</a> shows the functions available for use with the <code class="type">macaddr8</code> type. The function <code class="literal"><code class="function">trunc(<code class="type">macaddr8</code>)</code></code> returns a MAC address with the last 5 bytes set to zero. This can be used to associate the remaining prefix with a manufacturer. </p><div class="table" id="MACADDR8-FUNCTIONS-TABLE"><p class="title"><strong>Table 9.39. <code class="type">macaddr8</code> Functions</strong></p><div class="table-contents"><table class="table" summary="macaddr8 Functions" border="1"><colgroup><col /><col /><col /><col /><col /></colgroup><thead><tr><th>Function</th><th>Return Type</th><th>Description</th><th>Example</th><th>Result</th></tr></thead><tbody><tr><td> <a id="id-1.5.8.17.11.2.2.1.1.1" class="indexterm"></a> <code class="literal"><code class="function">trunc(<code class="type">macaddr8</code>)</code></code> </td><td><code class="type">macaddr8</code></td><td>set last 5 bytes to zero</td><td><code class="literal">trunc(macaddr8 '12:34:56:78:90:ab:cd:ef')</code></td><td><code class="literal">12:34:56:00:00:00:00:00</code></td></tr><tr><td> <a id="id-1.5.8.17.11.2.2.2.1.1" class="indexterm"></a> <code class="literal"><code class="function">macaddr8_set7bit(<code class="type">macaddr8</code>)</code></code> </td><td><code class="type">macaddr8</code></td><td>set 7th bit to one, also known as modified EUI-64, for inclusion in an IPv6 address</td><td><code class="literal">macaddr8_set7bit(macaddr8 '00:34:56:ab:cd:ef')</code></td><td><code class="literal">02:34:56:ff:fe:ab:cd:ef</code></td></tr></tbody></table></div></div><br class="table-break" /><p> The <code class="type">macaddr8</code> type also supports the standard relational operators (<code class="literal">></code>, <code class="literal"><=</code>, etc.) for ordering, and the bitwise arithmetic operators (<code class="literal">~</code>, <code class="literal">&</code> and <code class="literal">|</code>) for NOT, AND and OR. </p></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="functions-geometry.html">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="functions.html">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="functions-textsearch.html">Next</a></td></tr><tr><td width="40%" align="left" valign="top">9.11. Geometric Functions and Operators </td><td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td><td width="40%" align="right" valign="top"> 9.13. Text Search Functions and Operators</td></tr></table></div></body></html>