1 <?xml version="1.0" encoding="UTF-8" standalone="no"?>
2 <!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.5. Binary String 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-string.html" title="9.4. String Functions and Operators" /><link rel="next" href="functions-bitstring.html" title="9.6. Bit String Functions and Operators" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">9.5. Binary String Functions and Operators</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="functions-string.html" title="9.4. String 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 18.0 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="functions-bitstring.html" title="9.6. Bit String Functions and Operators">Next</a></td></tr></table><hr /></div><div class="sect1" id="FUNCTIONS-BINARYSTRING"><div class="titlepage"><div><div><h2 class="title" style="clear: both">9.5. Binary String Functions and Operators <a href="#FUNCTIONS-BINARYSTRING" class="id_link">#</a></h2></div></div></div><a id="id-1.5.8.11.2" class="indexterm"></a><p>
3 This section describes functions and operators for examining and
4 manipulating binary strings, that is values of type <code class="type">bytea</code>.
5 Many of these are equivalent, in purpose and syntax, to the
6 text-string functions described in the previous section.
8 <acronym class="acronym">SQL</acronym> defines some string functions that use
9 key words, rather than commas, to separate
10 arguments. Details are in
11 <a class="xref" href="functions-binarystring.html#FUNCTIONS-BINARYSTRING-SQL" title="Table 9.11. SQL Binary String Functions and Operators">Table 9.11</a>.
12 <span class="productname">PostgreSQL</span> also provides versions of these functions
13 that use the regular function invocation syntax
14 (see <a class="xref" href="functions-binarystring.html#FUNCTIONS-BINARYSTRING-OTHER" title="Table 9.12. Other Binary String Functions">Table 9.12</a>).
15 </p><div class="table" id="FUNCTIONS-BINARYSTRING-SQL"><p class="title"><strong>Table 9.11. <acronym class="acronym">SQL</acronym> Binary String Functions and Operators</strong></p><div class="table-contents"><table class="table" summary="SQL Binary String Functions and Operators" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">
23 </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">
24 <a id="id-1.5.8.11.5.2.2.1.1.1.1" class="indexterm"></a>
25 <code class="type">bytea</code> <code class="literal">||</code> <code class="type">bytea</code>
26 → <code class="returnvalue">bytea</code>
29 Concatenates the two binary strings.
32 <code class="literal">'\x123456'::bytea || '\x789a00bcde'::bytea</code>
33 → <code class="returnvalue">\x123456789a00bcde</code>
34 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
35 <a id="id-1.5.8.11.5.2.2.2.1.1.1" class="indexterm"></a>
36 <code class="function">bit_length</code> ( <code class="type">bytea</code> )
37 → <code class="returnvalue">integer</code>
40 Returns number of bits in the binary string (8
41 times the <code class="function">octet_length</code>).
44 <code class="literal">bit_length('\x123456'::bytea)</code>
45 → <code class="returnvalue">24</code>
46 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
47 <a id="id-1.5.8.11.5.2.2.3.1.1.1" class="indexterm"></a>
48 <code class="function">btrim</code> ( <em class="parameter"><code>bytes</code></em> <code class="type">bytea</code>,
49 <em class="parameter"><code>bytesremoved</code></em> <code class="type">bytea</code> )
50 → <code class="returnvalue">bytea</code>
53 Removes the longest string containing only bytes appearing in
54 <em class="parameter"><code>bytesremoved</code></em> from the start and end of
55 <em class="parameter"><code>bytes</code></em>.
58 <code class="literal">btrim('\x1234567890'::bytea, '\x9012'::bytea)</code>
59 → <code class="returnvalue">\x345678</code>
60 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
61 <a id="id-1.5.8.11.5.2.2.4.1.1.1" class="indexterm"></a>
62 <code class="function">ltrim</code> ( <em class="parameter"><code>bytes</code></em> <code class="type">bytea</code>,
63 <em class="parameter"><code>bytesremoved</code></em> <code class="type">bytea</code> )
64 → <code class="returnvalue">bytea</code>
67 Removes the longest string containing only bytes appearing in
68 <em class="parameter"><code>bytesremoved</code></em> from the start of
69 <em class="parameter"><code>bytes</code></em>.
72 <code class="literal">ltrim('\x1234567890'::bytea, '\x9012'::bytea)</code>
73 → <code class="returnvalue">\x34567890</code>
74 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
75 <a id="id-1.5.8.11.5.2.2.5.1.1.1" class="indexterm"></a>
76 <code class="function">octet_length</code> ( <code class="type">bytea</code> )
77 → <code class="returnvalue">integer</code>
80 Returns number of bytes in the binary string.
83 <code class="literal">octet_length('\x123456'::bytea)</code>
84 → <code class="returnvalue">3</code>
85 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
86 <a id="id-1.5.8.11.5.2.2.6.1.1.1" class="indexterm"></a>
87 <code class="function">overlay</code> ( <em class="parameter"><code>bytes</code></em> <code class="type">bytea</code> <code class="literal">PLACING</code> <em class="parameter"><code>newsubstring</code></em> <code class="type">bytea</code> <code class="literal">FROM</code> <em class="parameter"><code>start</code></em> <code class="type">integer</code> [<span class="optional"> <code class="literal">FOR</code> <em class="parameter"><code>count</code></em> <code class="type">integer</code> </span>] )
88 → <code class="returnvalue">bytea</code>
91 Replaces the substring of <em class="parameter"><code>bytes</code></em> that starts at
92 the <em class="parameter"><code>start</code></em>'th byte and extends
93 for <em class="parameter"><code>count</code></em> bytes
94 with <em class="parameter"><code>newsubstring</code></em>.
95 If <em class="parameter"><code>count</code></em> is omitted, it defaults to the length
96 of <em class="parameter"><code>newsubstring</code></em>.
99 <code class="literal">overlay('\x1234567890'::bytea placing '\002\003'::bytea from 2 for 3)</code>
100 → <code class="returnvalue">\x12020390</code>
101 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
102 <a id="id-1.5.8.11.5.2.2.7.1.1.1" class="indexterm"></a>
103 <code class="function">position</code> ( <em class="parameter"><code>substring</code></em> <code class="type">bytea</code> <code class="literal">IN</code> <em class="parameter"><code>bytes</code></em> <code class="type">bytea</code> )
104 → <code class="returnvalue">integer</code>
107 Returns first starting index of the specified
108 <em class="parameter"><code>substring</code></em> within
109 <em class="parameter"><code>bytes</code></em>, or zero if it's not present.
112 <code class="literal">position('\x5678'::bytea in '\x1234567890'::bytea)</code>
113 → <code class="returnvalue">3</code>
114 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
115 <a id="id-1.5.8.11.5.2.2.8.1.1.1" class="indexterm"></a>
116 <code class="function">rtrim</code> ( <em class="parameter"><code>bytes</code></em> <code class="type">bytea</code>,
117 <em class="parameter"><code>bytesremoved</code></em> <code class="type">bytea</code> )
118 → <code class="returnvalue">bytea</code>
121 Removes the longest string containing only bytes appearing in
122 <em class="parameter"><code>bytesremoved</code></em> from the end of
123 <em class="parameter"><code>bytes</code></em>.
126 <code class="literal">rtrim('\x1234567890'::bytea, '\x9012'::bytea)</code>
127 → <code class="returnvalue">\x12345678</code>
128 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
129 <a id="id-1.5.8.11.5.2.2.9.1.1.1" class="indexterm"></a>
130 <code class="function">substring</code> ( <em class="parameter"><code>bytes</code></em> <code class="type">bytea</code> [<span class="optional"> <code class="literal">FROM</code> <em class="parameter"><code>start</code></em> <code class="type">integer</code> </span>] [<span class="optional"> <code class="literal">FOR</code> <em class="parameter"><code>count</code></em> <code class="type">integer</code> </span>] )
131 → <code class="returnvalue">bytea</code>
134 Extracts the substring of <em class="parameter"><code>bytes</code></em> starting at
135 the <em class="parameter"><code>start</code></em>'th byte if that is specified,
136 and stopping after <em class="parameter"><code>count</code></em> bytes if that is
137 specified. Provide at least one of <em class="parameter"><code>start</code></em>
138 and <em class="parameter"><code>count</code></em>.
141 <code class="literal">substring('\x1234567890'::bytea from 3 for 2)</code>
142 → <code class="returnvalue">\x5678</code>
143 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
144 <a id="id-1.5.8.11.5.2.2.10.1.1.1" class="indexterm"></a>
145 <code class="function">trim</code> ( [<span class="optional"> <code class="literal">LEADING</code> | <code class="literal">TRAILING</code> | <code class="literal">BOTH</code> </span>]
146 <em class="parameter"><code>bytesremoved</code></em> <code class="type">bytea</code> <code class="literal">FROM</code>
147 <em class="parameter"><code>bytes</code></em> <code class="type">bytea</code> )
148 → <code class="returnvalue">bytea</code>
151 Removes the longest string containing only bytes appearing in
152 <em class="parameter"><code>bytesremoved</code></em> from the start,
153 end, or both ends (<code class="literal">BOTH</code> is the default)
154 of <em class="parameter"><code>bytes</code></em>.
157 <code class="literal">trim('\x9012'::bytea from '\x1234567890'::bytea)</code>
158 → <code class="returnvalue">\x345678</code>
159 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
160 <code class="function">trim</code> ( [<span class="optional"> <code class="literal">LEADING</code> | <code class="literal">TRAILING</code> | <code class="literal">BOTH</code> </span>] [<span class="optional"> <code class="literal">FROM</code> </span>]
161 <em class="parameter"><code>bytes</code></em> <code class="type">bytea</code>,
162 <em class="parameter"><code>bytesremoved</code></em> <code class="type">bytea</code> )
163 → <code class="returnvalue">bytea</code>
166 This is a non-standard syntax for <code class="function">trim()</code>.
169 <code class="literal">trim(both from '\x1234567890'::bytea, '\x9012'::bytea)</code>
170 → <code class="returnvalue">\x345678</code>
171 </p></td></tr></tbody></table></div></div><br class="table-break" /><p>
172 Additional binary string manipulation functions are available and
173 are listed in <a class="xref" href="functions-binarystring.html#FUNCTIONS-BINARYSTRING-OTHER" title="Table 9.12. Other Binary String Functions">Table 9.12</a>. Some
174 of them are used internally to implement the
175 <acronym class="acronym">SQL</acronym>-standard string functions listed in <a class="xref" href="functions-binarystring.html#FUNCTIONS-BINARYSTRING-SQL" title="Table 9.11. SQL Binary String Functions and Operators">Table 9.11</a>.
176 </p><div class="table" id="FUNCTIONS-BINARYSTRING-OTHER"><p class="title"><strong>Table 9.12. Other Binary String Functions</strong></p><div class="table-contents"><table class="table" summary="Other Binary String Functions" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">
184 </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">
185 <a id="id-1.5.8.11.7.2.2.1.1.1.1" class="indexterm"></a>
186 <a id="id-1.5.8.11.7.2.2.1.1.1.2" class="indexterm"></a>
187 <code class="function">bit_count</code> ( <em class="parameter"><code>bytes</code></em> <code class="type">bytea</code> )
188 → <code class="returnvalue">bigint</code>
191 Returns the number of bits set in the binary string (also known as
192 <span class="quote">“<span class="quote">popcount</span>”</span>).
195 <code class="literal">bit_count('\x1234567890'::bytea)</code>
196 → <code class="returnvalue">15</code>
197 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
198 <a id="id-1.5.8.11.7.2.2.2.1.1.1" class="indexterm"></a>
199 <code class="function">crc32</code> ( <code class="type">bytea</code> )
200 → <code class="returnvalue">bigint</code>
203 Computes the CRC-32 value of the binary string.
206 <code class="literal">crc32('abc'::bytea)</code>
207 → <code class="returnvalue">891568578</code>
208 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
209 <a id="id-1.5.8.11.7.2.2.3.1.1.1" class="indexterm"></a>
210 <code class="function">crc32c</code> ( <code class="type">bytea</code> )
211 → <code class="returnvalue">bigint</code>
214 Computes the CRC-32C value of the binary string.
217 <code class="literal">crc32c('abc'::bytea)</code>
218 → <code class="returnvalue">910901175</code>
219 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
220 <a id="id-1.5.8.11.7.2.2.4.1.1.1" class="indexterm"></a>
221 <code class="function">get_bit</code> ( <em class="parameter"><code>bytes</code></em> <code class="type">bytea</code>,
222 <em class="parameter"><code>n</code></em> <code class="type">bigint</code> )
223 → <code class="returnvalue">integer</code>
226 Extracts <a class="link" href="functions-binarystring.html#FUNCTIONS-ZEROBASED-NOTE">n'th</a> bit
230 <code class="literal">get_bit('\x1234567890'::bytea, 30)</code>
231 → <code class="returnvalue">1</code>
232 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
233 <a id="id-1.5.8.11.7.2.2.5.1.1.1" class="indexterm"></a>
234 <code class="function">get_byte</code> ( <em class="parameter"><code>bytes</code></em> <code class="type">bytea</code>,
235 <em class="parameter"><code>n</code></em> <code class="type">integer</code> )
236 → <code class="returnvalue">integer</code>
239 Extracts <a class="link" href="functions-binarystring.html#FUNCTIONS-ZEROBASED-NOTE">n'th</a> byte
243 <code class="literal">get_byte('\x1234567890'::bytea, 4)</code>
244 → <code class="returnvalue">144</code>
245 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
246 <a id="id-1.5.8.11.7.2.2.6.1.1.1" class="indexterm"></a>
247 <a id="id-1.5.8.11.7.2.2.6.1.1.2" class="indexterm"></a>
248 <a id="id-1.5.8.11.7.2.2.6.1.1.3" class="indexterm"></a>
249 <code class="function">length</code> ( <code class="type">bytea</code> )
250 → <code class="returnvalue">integer</code>
253 Returns the number of bytes in the binary string.
256 <code class="literal">length('\x1234567890'::bytea)</code>
257 → <code class="returnvalue">5</code>
258 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
259 <code class="function">length</code> ( <em class="parameter"><code>bytes</code></em> <code class="type">bytea</code>,
260 <em class="parameter"><code>encoding</code></em> <code class="type">name</code> )
261 → <code class="returnvalue">integer</code>
264 Returns the number of characters in the binary string, assuming
265 that it is text in the given <em class="parameter"><code>encoding</code></em>.
268 <code class="literal">length('jose'::bytea, 'UTF8')</code>
269 → <code class="returnvalue">4</code>
270 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
271 <a id="id-1.5.8.11.7.2.2.8.1.1.1" class="indexterm"></a>
272 <code class="function">md5</code> ( <code class="type">bytea</code> )
273 → <code class="returnvalue">text</code>
276 Computes the MD5 <a class="link" href="functions-binarystring.html#FUNCTIONS-HASH-NOTE">hash</a> of
277 the binary string, with the result written in hexadecimal.
280 <code class="literal">md5('Th\000omas'::bytea)</code>
281 → <code class="returnvalue">8ab2d3c9689aaf18b4958c334c82d8b1</code>
282 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
283 <a id="id-1.5.8.11.7.2.2.9.1.1.1" class="indexterm"></a>
284 <code class="function">reverse</code> ( <code class="type">bytea</code> )
285 → <code class="returnvalue">bytea</code>
288 Reverses the order of the bytes in the binary string.
291 <code class="literal">reverse('\xabcd'::bytea)</code>
292 → <code class="returnvalue">\xcdab</code>
293 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
294 <a id="id-1.5.8.11.7.2.2.10.1.1.1" class="indexterm"></a>
295 <code class="function">set_bit</code> ( <em class="parameter"><code>bytes</code></em> <code class="type">bytea</code>,
296 <em class="parameter"><code>n</code></em> <code class="type">bigint</code>,
297 <em class="parameter"><code>newvalue</code></em> <code class="type">integer</code> )
298 → <code class="returnvalue">bytea</code>
301 Sets <a class="link" href="functions-binarystring.html#FUNCTIONS-ZEROBASED-NOTE">n'th</a> bit in
302 binary string to <em class="parameter"><code>newvalue</code></em>.
305 <code class="literal">set_bit('\x1234567890'::bytea, 30, 0)</code>
306 → <code class="returnvalue">\x1234563890</code>
307 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
308 <a id="id-1.5.8.11.7.2.2.11.1.1.1" class="indexterm"></a>
309 <code class="function">set_byte</code> ( <em class="parameter"><code>bytes</code></em> <code class="type">bytea</code>,
310 <em class="parameter"><code>n</code></em> <code class="type">integer</code>,
311 <em class="parameter"><code>newvalue</code></em> <code class="type">integer</code> )
312 → <code class="returnvalue">bytea</code>
315 Sets <a class="link" href="functions-binarystring.html#FUNCTIONS-ZEROBASED-NOTE">n'th</a> byte in
316 binary string to <em class="parameter"><code>newvalue</code></em>.
319 <code class="literal">set_byte('\x1234567890'::bytea, 4, 64)</code>
320 → <code class="returnvalue">\x1234567840</code>
321 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
322 <a id="id-1.5.8.11.7.2.2.12.1.1.1" class="indexterm"></a>
323 <code class="function">sha224</code> ( <code class="type">bytea</code> )
324 → <code class="returnvalue">bytea</code>
327 Computes the SHA-224 <a class="link" href="functions-binarystring.html#FUNCTIONS-HASH-NOTE">hash</a>
328 of the binary string.
331 <code class="literal">sha224('abc'::bytea)</code>
332 → <code class="returnvalue">\x23097d223405d8228642a477bda255b32aadbce4bda0b3f7e36c9da7</code>
333 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
334 <a id="id-1.5.8.11.7.2.2.13.1.1.1" class="indexterm"></a>
335 <code class="function">sha256</code> ( <code class="type">bytea</code> )
336 → <code class="returnvalue">bytea</code>
339 Computes the SHA-256 <a class="link" href="functions-binarystring.html#FUNCTIONS-HASH-NOTE">hash</a>
340 of the binary string.
343 <code class="literal">sha256('abc'::bytea)</code>
344 → <code class="returnvalue">\xba7816bf8f01cfea414140de5dae2223b00361a396177a9cb410ff61f20015ad</code>
345 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
346 <a id="id-1.5.8.11.7.2.2.14.1.1.1" class="indexterm"></a>
347 <code class="function">sha384</code> ( <code class="type">bytea</code> )
348 → <code class="returnvalue">bytea</code>
351 Computes the SHA-384 <a class="link" href="functions-binarystring.html#FUNCTIONS-HASH-NOTE">hash</a>
352 of the binary string.
355 <code class="literal">sha384('abc'::bytea)</code>
356 → <code class="returnvalue">\xcb00753f45a35e8bb5a03d699ac65007272c32ab0eded1631a8b605a43ff5bed8086072ba1e7cc2358baeca134c825a7</code>
357 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
358 <a id="id-1.5.8.11.7.2.2.15.1.1.1" class="indexterm"></a>
359 <code class="function">sha512</code> ( <code class="type">bytea</code> )
360 → <code class="returnvalue">bytea</code>
363 Computes the SHA-512 <a class="link" href="functions-binarystring.html#FUNCTIONS-HASH-NOTE">hash</a>
364 of the binary string.
367 <code class="literal">sha512('abc'::bytea)</code>
368 → <code class="returnvalue">\xddaf35a193617abacc417349ae20413112e6fa4e89a97ea20a9eeee64b55d39a2192992a274fc1a836ba3c23a3feebbd454d4423643ce80e2a9ac94fa54ca49f</code>
369 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
370 <a id="id-1.5.8.11.7.2.2.16.1.1.1" class="indexterm"></a>
371 <code class="function">substr</code> ( <em class="parameter"><code>bytes</code></em> <code class="type">bytea</code>, <em class="parameter"><code>start</code></em> <code class="type">integer</code> [<span class="optional">, <em class="parameter"><code>count</code></em> <code class="type">integer</code> </span>] )
372 → <code class="returnvalue">bytea</code>
375 Extracts the substring of <em class="parameter"><code>bytes</code></em> starting at
376 the <em class="parameter"><code>start</code></em>'th byte,
377 and extending for <em class="parameter"><code>count</code></em> bytes if that is
379 as <code class="literal">substring(<em class="parameter"><code>bytes</code></em>
380 from <em class="parameter"><code>start</code></em>
381 for <em class="parameter"><code>count</code></em>)</code>.)
384 <code class="literal">substr('\x1234567890'::bytea, 3, 2)</code>
385 → <code class="returnvalue">\x5678</code>
386 </p></td></tr></tbody></table></div></div><br class="table-break" /><p id="FUNCTIONS-ZEROBASED-NOTE">
387 Functions <code class="function">get_byte</code> and <code class="function">set_byte</code>
388 number the first byte of a binary string as byte 0.
389 Functions <code class="function">get_bit</code> and <code class="function">set_bit</code>
390 number bits from the right within each byte; for example bit 0 is the least
391 significant bit of the first byte, and bit 15 is the most significant bit
393 </p><p id="FUNCTIONS-HASH-NOTE">
394 For historical reasons, the function <code class="function">md5</code>
395 returns a hex-encoded value of type <code class="type">text</code> whereas the SHA-2
396 functions return type <code class="type">bytea</code>. Use the functions
397 <a class="link" href="functions-binarystring.html#FUNCTION-ENCODE"><code class="function">encode</code></a>
398 and <a class="link" href="functions-binarystring.html#FUNCTION-DECODE"><code class="function">decode</code></a> to
399 convert between the two. For example write <code class="literal">encode(sha256('abc'),
400 'hex')</code> to get a hex-encoded text representation,
401 or <code class="literal">decode(md5('abc'), 'hex')</code> to get
402 a <code class="type">bytea</code> value.
404 <a id="id-1.5.8.11.10.1" class="indexterm"></a>
405 <a id="id-1.5.8.11.10.2" class="indexterm"></a>
406 Functions for converting strings between different character sets
407 (encodings), and for representing arbitrary binary data in textual
409 <a class="xref" href="functions-binarystring.html#FUNCTIONS-BINARYSTRING-CONVERSIONS" title="Table 9.13. Text/Binary String Conversion Functions">Table 9.13</a>. For these
410 functions, an argument or result of type <code class="type">text</code> is expressed
411 in the database's default encoding, while arguments or results of
412 type <code class="type">bytea</code> are in an encoding named by another argument.
413 </p><div class="table" id="FUNCTIONS-BINARYSTRING-CONVERSIONS"><p class="title"><strong>Table 9.13. Text/Binary String Conversion Functions</strong></p><div class="table-contents"><table class="table" summary="Text/Binary String Conversion Functions" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">
421 </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">
422 <a id="id-1.5.8.11.11.2.2.1.1.1.1" class="indexterm"></a>
423 <code class="function">convert</code> ( <em class="parameter"><code>bytes</code></em> <code class="type">bytea</code>,
424 <em class="parameter"><code>src_encoding</code></em> <code class="type">name</code>,
425 <em class="parameter"><code>dest_encoding</code></em> <code class="type">name</code> )
426 → <code class="returnvalue">bytea</code>
429 Converts a binary string representing text in
430 encoding <em class="parameter"><code>src_encoding</code></em>
431 to a binary string in encoding <em class="parameter"><code>dest_encoding</code></em>
432 (see <a class="xref" href="multibyte.html#MULTIBYTE-CONVERSIONS-SUPPORTED" title="23.3.4. Available Character Set Conversions">Section 23.3.4</a> for
433 available conversions).
436 <code class="literal">convert('text_in_utf8', 'UTF8', 'LATIN1')</code>
437 → <code class="returnvalue">\x746578745f696e5f75746638</code>
438 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
439 <a id="id-1.5.8.11.11.2.2.2.1.1.1" class="indexterm"></a>
440 <code class="function">convert_from</code> ( <em class="parameter"><code>bytes</code></em> <code class="type">bytea</code>,
441 <em class="parameter"><code>src_encoding</code></em> <code class="type">name</code> )
442 → <code class="returnvalue">text</code>
445 Converts a binary string representing text in
446 encoding <em class="parameter"><code>src_encoding</code></em>
447 to <code class="type">text</code> in the database encoding
448 (see <a class="xref" href="multibyte.html#MULTIBYTE-CONVERSIONS-SUPPORTED" title="23.3.4. Available Character Set Conversions">Section 23.3.4</a> for
449 available conversions).
452 <code class="literal">convert_from('text_in_utf8', 'UTF8')</code>
453 → <code class="returnvalue">text_in_utf8</code>
454 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
455 <a id="id-1.5.8.11.11.2.2.3.1.1.1" class="indexterm"></a>
456 <code class="function">convert_to</code> ( <em class="parameter"><code>string</code></em> <code class="type">text</code>,
457 <em class="parameter"><code>dest_encoding</code></em> <code class="type">name</code> )
458 → <code class="returnvalue">bytea</code>
461 Converts a <code class="type">text</code> string (in the database encoding) to a
462 binary string encoded in encoding <em class="parameter"><code>dest_encoding</code></em>
463 (see <a class="xref" href="multibyte.html#MULTIBYTE-CONVERSIONS-SUPPORTED" title="23.3.4. Available Character Set Conversions">Section 23.3.4</a> for
464 available conversions).
467 <code class="literal">convert_to('some_text', 'UTF8')</code>
468 → <code class="returnvalue">\x736f6d655f74657874</code>
469 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
470 <a id="FUNCTION-ENCODE" class="indexterm"></a>
471 <code class="function">encode</code> ( <em class="parameter"><code>bytes</code></em> <code class="type">bytea</code>,
472 <em class="parameter"><code>format</code></em> <code class="type">text</code> )
473 → <code class="returnvalue">text</code>
476 Encodes binary data into a textual representation; supported
477 <em class="parameter"><code>format</code></em> values are:
478 <a class="link" href="functions-binarystring.html#ENCODE-FORMAT-BASE64"><code class="literal">base64</code></a>,
479 <a class="link" href="functions-binarystring.html#ENCODE-FORMAT-ESCAPE"><code class="literal">escape</code></a>,
480 <a class="link" href="functions-binarystring.html#ENCODE-FORMAT-HEX"><code class="literal">hex</code></a>.
483 <code class="literal">encode('123\000\001', 'base64')</code>
484 → <code class="returnvalue">MTIzAAE=</code>
485 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
486 <a id="FUNCTION-DECODE" class="indexterm"></a>
487 <code class="function">decode</code> ( <em class="parameter"><code>string</code></em> <code class="type">text</code>,
488 <em class="parameter"><code>format</code></em> <code class="type">text</code> )
489 → <code class="returnvalue">bytea</code>
492 Decodes binary data from a textual representation; supported
493 <em class="parameter"><code>format</code></em> values are the same as
494 for <code class="function">encode</code>.
497 <code class="literal">decode('MTIzAAE=', 'base64')</code>
498 → <code class="returnvalue">\x3132330001</code>
499 </p></td></tr></tbody></table></div></div><br class="table-break" /><p>
500 The <code class="function">encode</code> and <code class="function">decode</code>
501 functions support the following textual formats:
503 </p><div class="variablelist"><dl class="variablelist"><dt id="ENCODE-FORMAT-BASE64"><span class="term">base64
504 <a id="id-1.5.8.11.12.3.1.1.1" class="indexterm"></a></span> <a href="#ENCODE-FORMAT-BASE64" class="id_link">#</a></dt><dd><p>
505 The <code class="literal">base64</code> format is that
506 of <a class="ulink" href="https://datatracker.ietf.org/doc/html/rfc2045#section-6.8" target="_top">RFC
507 2045 Section 6.8</a>. As per the <acronym class="acronym">RFC</acronym>, encoded lines are
508 broken at 76 characters. However instead of the MIME CRLF
509 end-of-line marker, only a newline is used for end-of-line.
510 The <code class="function">decode</code> function ignores carriage-return,
511 newline, space, and tab characters. Otherwise, an error is
512 raised when <code class="function">decode</code> is supplied invalid
513 base64 data — including when trailing padding is incorrect.
514 </p></dd><dt id="ENCODE-FORMAT-ESCAPE"><span class="term">escape
515 <a id="id-1.5.8.11.12.3.2.1.1" class="indexterm"></a></span> <a href="#ENCODE-FORMAT-ESCAPE" class="id_link">#</a></dt><dd><p>
516 The <code class="literal">escape</code> format converts zero bytes and
517 bytes with the high bit set into octal escape sequences
518 (<code class="literal">\</code><em class="replaceable"><code>nnn</code></em>), and it doubles
519 backslashes. Other byte values are represented literally.
520 The <code class="function">decode</code> function will raise an error if a
521 backslash is not followed by either a second backslash or three
522 octal digits; it accepts other byte values unchanged.
523 </p></dd><dt id="ENCODE-FORMAT-HEX"><span class="term">hex
524 <a id="id-1.5.8.11.12.3.3.1.1" class="indexterm"></a></span> <a href="#ENCODE-FORMAT-HEX" class="id_link">#</a></dt><dd><p>
525 The <code class="literal">hex</code> format represents each 4 bits of
526 data as one hexadecimal digit, <code class="literal">0</code>
527 through <code class="literal">f</code>, writing the higher-order digit of
528 each byte first. The <code class="function">encode</code> function outputs
529 the <code class="literal">a</code>-<code class="literal">f</code> hex digits in lower
530 case. Because the smallest unit of data is 8 bits, there are
531 always an even number of characters returned
532 by <code class="function">encode</code>.
533 The <code class="function">decode</code> function
534 accepts the <code class="literal">a</code>-<code class="literal">f</code> characters in
535 either upper or lower case. An error is raised
536 when <code class="function">decode</code> is given invalid hex data
537 — including when given an odd number of characters.
538 </p></dd></dl></div><p>
540 In addition, it is possible to cast integral values to and from type
541 <code class="type">bytea</code>. Casting an integer to <code class="type">bytea</code> produces
542 2, 4, or 8 bytes, depending on the width of the integer type. The result
543 is the two's complement representation of the integer, with the most
544 significant byte first. Some examples:
545 </p><pre class="programlisting">
546 1234::smallint::bytea <em class="lineannotation"><span class="lineannotation">\x04d2</span></em>
547 cast(1234 as bytea) <em class="lineannotation"><span class="lineannotation">\x000004d2</span></em>
548 cast(-1234 as bytea) <em class="lineannotation"><span class="lineannotation">\xfffffb2e</span></em>
549 '\x8000'::bytea::smallint <em class="lineannotation"><span class="lineannotation">-32768</span></em>
550 '\x8000'::bytea::integer <em class="lineannotation"><span class="lineannotation">32768</span></em>
552 Casting a <code class="type">bytea</code> to an integer will raise an error if the
553 length of the <code class="type">bytea</code> exceeds the width of the integer type.
555 See also the aggregate function <code class="function">string_agg</code> in
556 <a class="xref" href="functions-aggregate.html" title="9.21. Aggregate Functions">Section 9.21</a> and the large object functions
557 in <a class="xref" href="lo-funcs.html" title="33.4. Server-Side Functions">Section 33.4</a>.
558 </p></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="functions-string.html" title="9.4. String 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-bitstring.html" title="9.6. Bit String Functions and Operators">Next</a></td></tr><tr><td width="40%" align="left" valign="top">9.4. String Functions and Operators </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 9.6. Bit String Functions and Operators</td></tr></table></div></body></html>
begriffs.com / projects / ai-pg / blob