1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
|
<?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 id="docContent" class="container-fluid col-10"><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 14.5 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>
The IP network address types, <code class="type">cidr</code> and <code class="type">inet</code>,
support the usual comparison operators shown in
<a class="xref" href="functions-comparison.html#FUNCTIONS-COMPARISON-OP-TABLE" title="Table 9.1. Comparison Operators">Table 9.1</a>
as well as the specialized operators and functions shown in
<a class="xref" href="functions-net.html#CIDR-INET-OPERATORS-TABLE" title="Table 9.38. IP Address Operators">Table 9.38</a> and
<a class="xref" href="functions-net.html#CIDR-INET-FUNCTIONS-TABLE" title="Table 9.39. IP Address Functions">Table 9.39</a>.
</p><p>
Any <code class="type">cidr</code> value can be cast to <code class="type">inet</code> implicitly;
therefore, the operators and functions shown below 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.
</p><div class="table" id="CIDR-INET-OPERATORS-TABLE"><p class="title"><strong>Table 9.38. IP Address Operators</strong></p><div class="table-contents"><table class="table" summary="IP Address Operators" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">
Operator
</p>
<p>
Description
</p>
<p>
Example(s)
</p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">
<code class="type">inet</code> <code class="literal"><<</code> <code class="type">inet</code>
→ <code class="returnvalue">boolean</code>
</p>
<p>
Is subnet strictly contained by subnet?
This operator, and the next four, test for subnet inclusion. They
consider only the network parts of the two addresses (ignoring any
bits to the right of the netmasks) and determine whether one network
is identical to or a subnet of the other.
</p>
<p>
<code class="literal">inet '192.168.1.5' << inet '192.168.1/24'</code>
→ <code class="returnvalue">t</code>
</p>
<p>
<code class="literal">inet '192.168.0.5' << inet '192.168.1/24'</code>
→ <code class="returnvalue">f</code>
</p>
<p>
<code class="literal">inet '192.168.1/24' << inet '192.168.1/24'</code>
→ <code class="returnvalue">f</code>
</p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
<code class="type">inet</code> <code class="literal"><<=</code> <code class="type">inet</code>
→ <code class="returnvalue">boolean</code>
</p>
<p>
Is subnet contained by or equal to subnet?
</p>
<p>
<code class="literal">inet '192.168.1/24' <<= inet '192.168.1/24'</code>
→ <code class="returnvalue">t</code>
</p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
<code class="type">inet</code> <code class="literal">>></code> <code class="type">inet</code>
→ <code class="returnvalue">boolean</code>
</p>
<p>
Does subnet strictly contain subnet?
</p>
<p>
<code class="literal">inet '192.168.1/24' >> inet '192.168.1.5'</code>
→ <code class="returnvalue">t</code>
</p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
<code class="type">inet</code> <code class="literal">>>=</code> <code class="type">inet</code>
→ <code class="returnvalue">boolean</code>
</p>
<p>
Does subnet contain or equal subnet?
</p>
<p>
<code class="literal">inet '192.168.1/24' >>= inet '192.168.1/24'</code>
→ <code class="returnvalue">t</code>
</p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
<code class="type">inet</code> <code class="literal">&&</code> <code class="type">inet</code>
→ <code class="returnvalue">boolean</code>
</p>
<p>
Does either subnet contain or equal the other?
</p>
<p>
<code class="literal">inet '192.168.1/24' && inet '192.168.1.80/28'</code>
→ <code class="returnvalue">t</code>
</p>
<p>
<code class="literal">inet '192.168.1/24' && inet '192.168.2.0/28'</code>
→ <code class="returnvalue">f</code>
</p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
<code class="literal">~</code> <code class="type">inet</code>
→ <code class="returnvalue">inet</code>
</p>
<p>
Computes bitwise NOT.
</p>
<p>
<code class="literal">~ inet '192.168.1.6'</code>
→ <code class="returnvalue">63.87.254.249</code>
</p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
<code class="type">inet</code> <code class="literal">&</code> <code class="type">inet</code>
→ <code class="returnvalue">inet</code>
</p>
<p>
Computes bitwise AND.
</p>
<p>
<code class="literal">inet '192.168.1.6' & inet '0.0.0.255'</code>
→ <code class="returnvalue">0.0.0.6</code>
</p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
<code class="type">inet</code> <code class="literal">|</code> <code class="type">inet</code>
→ <code class="returnvalue">inet</code>
</p>
<p>
Computes bitwise OR.
</p>
<p>
<code class="literal">inet '192.168.1.6' | inet '0.0.0.255'</code>
→ <code class="returnvalue">192.168.1.255</code>
</p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
<code class="type">inet</code> <code class="literal">+</code> <code class="type">bigint</code>
→ <code class="returnvalue">inet</code>
</p>
<p>
Adds an offset to an address.
</p>
<p>
<code class="literal">inet '192.168.1.6' + 25</code>
→ <code class="returnvalue">192.168.1.31</code>
</p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
<code class="type">bigint</code> <code class="literal">+</code> <code class="type">inet</code>
→ <code class="returnvalue">inet</code>
</p>
<p>
Adds an offset to an address.
</p>
<p>
<code class="literal">200 + inet '::ffff:fff0:1'</code>
→ <code class="returnvalue">::ffff:255.240.0.201</code>
</p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
<code class="type">inet</code> <code class="literal">-</code> <code class="type">bigint</code>
→ <code class="returnvalue">inet</code>
</p>
<p>
Subtracts an offset from an address.
</p>
<p>
<code class="literal">inet '192.168.1.43' - 36</code>
→ <code class="returnvalue">192.168.1.7</code>
</p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
<code class="type">inet</code> <code class="literal">-</code> <code class="type">inet</code>
→ <code class="returnvalue">bigint</code>
</p>
<p>
Computes the difference of two addresses.
</p>
<p>
<code class="literal">inet '192.168.1.43' - inet '192.168.1.19'</code>
→ <code class="returnvalue">24</code>
</p>
<p>
<code class="literal">inet '::1' - inet '::ffff:1'</code>
→ <code class="returnvalue">-4294901760</code>
</p></td></tr></tbody></table></div></div><br class="table-break" /><div class="table" id="CIDR-INET-FUNCTIONS-TABLE"><p class="title"><strong>Table 9.39. IP Address Functions</strong></p><div class="table-contents"><table class="table" summary="IP Address Functions" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">
Function
</p>
<p>
Description
</p>
<p>
Example(s)
</p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.5.8.18.5.2.2.1.1.1.1" class="indexterm"></a>
<code class="function">abbrev</code> ( <code class="type">inet</code> )
→ <code class="returnvalue">text</code>
</p>
<p>
Creates an abbreviated display format as text.
(The result is the same as the <code class="type">inet</code> output function
produces; it is <span class="quote">“<span class="quote">abbreviated</span>”</span> only in comparison to the
result of an explicit cast to <code class="type">text</code>, which for historical
reasons will never suppress the netmask part.)
</p>
<p>
<code class="literal">abbrev(inet '10.1.0.0/32')</code>
→ <code class="returnvalue">10.1.0.0</code>
</p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
<code class="function">abbrev</code> ( <code class="type">cidr</code> )
→ <code class="returnvalue">text</code>
</p>
<p>
Creates an abbreviated display format as text.
(The abbreviation consists of dropping all-zero octets to the right
of the netmask; more examples are in
<a class="xref" href="datatype-net-types.html#DATATYPE-NET-CIDR-TABLE" title="Table 8.22. cidr Type Input Examples">Table 8.22</a>.)
</p>
<p>
<code class="literal">abbrev(cidr '10.1.0.0/16')</code>
→ <code class="returnvalue">10.1/16</code>
</p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.5.8.18.5.2.2.3.1.1.1" class="indexterm"></a>
<code class="function">broadcast</code> ( <code class="type">inet</code> )
→ <code class="returnvalue">inet</code>
</p>
<p>
Computes the broadcast address for the address's network.
</p>
<p>
<code class="literal">broadcast(inet '192.168.1.5/24')</code>
→ <code class="returnvalue">192.168.1.255/24</code>
</p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.5.8.18.5.2.2.4.1.1.1" class="indexterm"></a>
<code class="function">family</code> ( <code class="type">inet</code> )
→ <code class="returnvalue">integer</code>
</p>
<p>
Returns the address's family: <code class="literal">4</code> for IPv4,
<code class="literal">6</code> for IPv6.
</p>
<p>
<code class="literal">family(inet '::1')</code>
→ <code class="returnvalue">6</code>
</p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.5.8.18.5.2.2.5.1.1.1" class="indexterm"></a>
<code class="function">host</code> ( <code class="type">inet</code> )
→ <code class="returnvalue">text</code>
</p>
<p>
Returns the IP address as text, ignoring the netmask.
</p>
<p>
<code class="literal">host(inet '192.168.1.0/24')</code>
→ <code class="returnvalue">192.168.1.0</code>
</p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.5.8.18.5.2.2.6.1.1.1" class="indexterm"></a>
<code class="function">hostmask</code> ( <code class="type">inet</code> )
→ <code class="returnvalue">inet</code>
</p>
<p>
Computes the host mask for the address's network.
</p>
<p>
<code class="literal">hostmask(inet '192.168.23.20/30')</code>
→ <code class="returnvalue">0.0.0.3</code>
</p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.5.8.18.5.2.2.7.1.1.1" class="indexterm"></a>
<code class="function">inet_merge</code> ( <code class="type">inet</code>, <code class="type">inet</code> )
→ <code class="returnvalue">cidr</code>
</p>
<p>
Computes the smallest network that includes both of the given networks.
</p>
<p>
<code class="literal">inet_merge(inet '192.168.1.5/24', inet '192.168.2.5/24')</code>
→ <code class="returnvalue">192.168.0.0/22</code>
</p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.5.8.18.5.2.2.8.1.1.1" class="indexterm"></a>
<code class="function">inet_same_family</code> ( <code class="type">inet</code>, <code class="type">inet</code> )
→ <code class="returnvalue">boolean</code>
</p>
<p>
Tests whether the addresses belong to the same IP family.
</p>
<p>
<code class="literal">inet_same_family(inet '192.168.1.5/24', inet '::1')</code>
→ <code class="returnvalue">f</code>
</p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.5.8.18.5.2.2.9.1.1.1" class="indexterm"></a>
<code class="function">masklen</code> ( <code class="type">inet</code> )
→ <code class="returnvalue">integer</code>
</p>
<p>
Returns the netmask length in bits.
</p>
<p>
<code class="literal">masklen(inet '192.168.1.5/24')</code>
→ <code class="returnvalue">24</code>
</p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.5.8.18.5.2.2.10.1.1.1" class="indexterm"></a>
<code class="function">netmask</code> ( <code class="type">inet</code> )
→ <code class="returnvalue">inet</code>
</p>
<p>
Computes the network mask for the address's network.
</p>
<p>
<code class="literal">netmask(inet '192.168.1.5/24')</code>
→ <code class="returnvalue">255.255.255.0</code>
</p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.5.8.18.5.2.2.11.1.1.1" class="indexterm"></a>
<code class="function">network</code> ( <code class="type">inet</code> )
→ <code class="returnvalue">cidr</code>
</p>
<p>
Returns the network part of the address, zeroing out
whatever is to the right of the netmask.
(This is equivalent to casting the value to <code class="type">cidr</code>.)
</p>
<p>
<code class="literal">network(inet '192.168.1.5/24')</code>
→ <code class="returnvalue">192.168.1.0/24</code>
</p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.5.8.18.5.2.2.12.1.1.1" class="indexterm"></a>
<code class="function">set_masklen</code> ( <code class="type">inet</code>, <code class="type">integer</code> )
→ <code class="returnvalue">inet</code>
</p>
<p>
Sets the netmask length for an <code class="type">inet</code> value.
The address part does not change.
</p>
<p>
<code class="literal">set_masklen(inet '192.168.1.5/24', 16)</code>
→ <code class="returnvalue">192.168.1.5/16</code>
</p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
<code class="function">set_masklen</code> ( <code class="type">cidr</code>, <code class="type">integer</code> )
→ <code class="returnvalue">cidr</code>
</p>
<p>
Sets the netmask length for a <code class="type">cidr</code> value.
Address bits to the right of the new netmask are set to zero.
</p>
<p>
<code class="literal">set_masklen(cidr '192.168.1.0/24', 16)</code>
→ <code class="returnvalue">192.168.0.0/16</code>
</p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.5.8.18.5.2.2.14.1.1.1" class="indexterm"></a>
<code class="function">text</code> ( <code class="type">inet</code> )
→ <code class="returnvalue">text</code>
</p>
<p>
Returns the unabbreviated IP address and netmask length as text.
(This has the same result as an explicit cast to <code class="type">text</code>.)
</p>
<p>
<code class="literal">text(inet '192.168.1.5')</code>
→ <code class="returnvalue">192.168.1.5/32</code>
</p></td></tr></tbody></table></div></div><br class="table-break" /><div class="tip"><h3 class="title">Tip</h3><p>
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 for IP addresses.
</p></div><p>
The MAC address types, <code class="type">macaddr</code> and <code class="type">macaddr8</code>,
support the usual comparison operators shown in
<a class="xref" href="functions-comparison.html#FUNCTIONS-COMPARISON-OP-TABLE" title="Table 9.1. Comparison Operators">Table 9.1</a>
as well as the specialized functions shown in
<a class="xref" href="functions-net.html#MACADDR-FUNCTIONS-TABLE" title="Table 9.40. MAC Address Functions">Table 9.40</a>.
In addition, they support the bitwise logical operators
<code class="literal">~</code>, <code class="literal">&</code> and <code class="literal">|</code>
(NOT, AND and OR), just as shown above for IP addresses.
</p><div class="table" id="MACADDR-FUNCTIONS-TABLE"><p class="title"><strong>Table 9.40. MAC Address Functions</strong></p><div class="table-contents"><table class="table" summary="MAC Address Functions" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">
Function
</p>
<p>
Description
</p>
<p>
Example(s)
</p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.5.8.18.8.2.2.1.1.1.1" class="indexterm"></a>
<code class="function">trunc</code> ( <code class="type">macaddr</code> )
→ <code class="returnvalue">macaddr</code>
</p>
<p>
Sets the last 3 bytes of the address to zero. The remaining prefix
can be associated with a particular manufacturer (using data not
included in <span class="productname">PostgreSQL</span>).
</p>
<p>
<code class="literal">trunc(macaddr '12:34:56:78:90:ab')</code>
→ <code class="returnvalue">12:34:56:00:00:00</code>
</p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
<code class="function">trunc</code> ( <code class="type">macaddr8</code> )
→ <code class="returnvalue">macaddr8</code>
</p>
<p>
Sets the last 5 bytes of the address to zero. The remaining prefix
can be associated with a particular manufacturer (using data not
included in <span class="productname">PostgreSQL</span>).
</p>
<p>
<code class="literal">trunc(macaddr8 '12:34:56:78:90:ab:cd:ef')</code>
→ <code class="returnvalue">12:34:56:00:00:00:00:00</code>
</p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.5.8.18.8.2.2.3.1.1.1" class="indexterm"></a>
<code class="function">macaddr8_set7bit</code> ( <code class="type">macaddr8</code> )
→ <code class="returnvalue">macaddr8</code>
</p>
<p>
Sets the 7th bit of the address to one, creating what is known as
modified EUI-64, for inclusion in an IPv6 address.
</p>
<p>
<code class="literal">macaddr8_set7bit(macaddr8 '00:34:56:ab:cd:ef')</code>
→ <code class="returnvalue">02:34:56:ff:fe:ab:cd:ef</code>
</p></td></tr></tbody></table></div></div><br class="table-break" /></div><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navfooter"><hr></hr><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="functions-geometry.html" title="9.11. Geometric Functions and Operators">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="functions.html" title="Chapter 9. Functions and Operators">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="functions-textsearch.html" title="9.13. Text Search Functions and Operators">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" title="PostgreSQL 14.5 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 9.13. Text Search Functions and Operators</td></tr></table></div></body></html>
|
