codekingpro/portable-devtools
115k
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 16.3 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 and4 manipulating binary strings, that is values of type <code class="type">bytea</code>.5 Many of these are equivalent, in purpose and syntax, to the6 text-string functions described in the previous section.7 </p><p>8 <acronym class="acronym">SQL</acronym> defines some string functions that use9 key words, rather than commas, to separate10 arguments. Details are in11 <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 functions13 that use the regular function invocation syntax14 (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">16 Function/Operator17 </p>18 <p>19 Description20 </p>21 <p>22 Example(s)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>27 </p>28 <p>29 Concatenates the two binary strings.30 </p>31 <p>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>38 </p>39 <p>40 Returns number of bits in the binary string (841 times the <code class="function">octet_length</code>).42 </p>43 <p>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>51 </p>52 <p>53 Removes the longest string containing only bytes appearing in54 <em class="parameter"><code>bytesremoved</code></em> from the start and end of55 <em class="parameter"><code>bytes</code></em>.56 </p>57 <p>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>65 </p>66 <p>67 Removes the longest string containing only bytes appearing in68 <em class="parameter"><code>bytesremoved</code></em> from the start of69 <em class="parameter"><code>bytes</code></em>.70 </p>71 <p>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>78 </p>79 <p>80 Returns number of bytes in the binary string.81 </p>82 <p>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>89 </p>90 <p>91 Replaces the substring of <em class="parameter"><code>bytes</code></em> that starts at92 the <em class="parameter"><code>start</code></em>'th byte and extends93 for <em class="parameter"><code>count</code></em> bytes94 with <em class="parameter"><code>newsubstring</code></em>.95 If <em class="parameter"><code>count</code></em> is omitted, it defaults to the length96 of <em class="parameter"><code>newsubstring</code></em>.97 </p>98 <p>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>105 </p>106 <p>107 Returns first starting index of the specified108 <em class="parameter"><code>substring</code></em> within109 <em class="parameter"><code>bytes</code></em>, or zero if it's not present.110 </p>111 <p>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>119 </p>120 <p>121 Removes the longest string containing only bytes appearing in122 <em class="parameter"><code>bytesremoved</code></em> from the end of123 <em class="parameter"><code>bytes</code></em>.124 </p>125 <p>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>132 </p>133 <p>134 Extracts the substring of <em class="parameter"><code>bytes</code></em> starting at135 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 is137 specified. Provide at least one of <em class="parameter"><code>start</code></em>138 and <em class="parameter"><code>count</code></em>.139 </p>140 <p>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>149 </p>150 <p>151 Removes the longest string containing only bytes appearing in152 <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>.155 </p>156 <p>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>164 </p>165 <p>166 This is a non-standard syntax for <code class="function">trim()</code>.167 </p>168 <p>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 and173 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>. Some174 of them are used internally to implement the175 <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">177 Function178 </p>179 <p>180 Description181 </p>182 <p>183 Example(s)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>189 </p>190 <p>191 Returns the number of bits set in the binary string (also known as192 <span class="quote">“<span class="quote">popcount</span>”</span>).193 </p>194 <p>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">get_bit</code> ( <em class="parameter"><code>bytes</code></em> <code class="type">bytea</code>,200 <em class="parameter"><code>n</code></em> <code class="type">bigint</code> )201 → <code class="returnvalue">integer</code>202 </p>203 <p>204 Extracts <a class="link" href="functions-binarystring.html#FUNCTIONS-ZEROBASED-NOTE">n'th</a> bit205 from binary string.206 </p>207 <p>208 <code class="literal">get_bit('\x1234567890'::bytea, 30)</code>209 → <code class="returnvalue">1</code>210 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">211 <a id="id-1.5.8.11.7.2.2.3.1.1.1" class="indexterm"></a>212 <code class="function">get_byte</code> ( <em class="parameter"><code>bytes</code></em> <code class="type">bytea</code>,213 <em class="parameter"><code>n</code></em> <code class="type">integer</code> )214 → <code class="returnvalue">integer</code>215 </p>216 <p>217 Extracts <a class="link" href="functions-binarystring.html#FUNCTIONS-ZEROBASED-NOTE">n'th</a> byte218 from binary string.219 </p>220 <p>221 <code class="literal">get_byte('\x1234567890'::bytea, 4)</code>222 → <code class="returnvalue">144</code>223 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">224 <a id="id-1.5.8.11.7.2.2.4.1.1.1" class="indexterm"></a>225 <a id="id-1.5.8.11.7.2.2.4.1.1.2" class="indexterm"></a>226 <a id="id-1.5.8.11.7.2.2.4.1.1.3" class="indexterm"></a>227 <code class="function">length</code> ( <code class="type">bytea</code> )228 → <code class="returnvalue">integer</code>229 </p>230 <p>231 Returns the number of bytes in the binary string.232 </p>233 <p>234 <code class="literal">length('\x1234567890'::bytea)</code>235 → <code class="returnvalue">5</code>236 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">237 <code class="function">length</code> ( <em class="parameter"><code>bytes</code></em> <code class="type">bytea</code>,238 <em class="parameter"><code>encoding</code></em> <code class="type">name</code> )239 → <code class="returnvalue">integer</code>240 </p>241 <p>242 Returns the number of characters in the binary string, assuming243 that it is text in the given <em class="parameter"><code>encoding</code></em>.244 </p>245 <p>246 <code class="literal">length('jose'::bytea, 'UTF8')</code>247 → <code class="returnvalue">4</code>248 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">249 <a id="id-1.5.8.11.7.2.2.6.1.1.1" class="indexterm"></a>250 <code class="function">md5</code> ( <code class="type">bytea</code> )251 → <code class="returnvalue">text</code>252 </p>253 <p>254 Computes the MD5 <a class="link" href="functions-binarystring.html#FUNCTIONS-HASH-NOTE">hash</a> of255 the binary string, with the result written in hexadecimal.256 </p>257 <p>258 <code class="literal">md5('Th\000omas'::bytea)</code>259 → <code class="returnvalue">8ab2d3c9689aaf18b4958c334c82d8b1</code>260 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">261 <a id="id-1.5.8.11.7.2.2.7.1.1.1" class="indexterm"></a>262 <code class="function">set_bit</code> ( <em class="parameter"><code>bytes</code></em> <code class="type">bytea</code>,263 <em class="parameter"><code>n</code></em> <code class="type">bigint</code>,264 <em class="parameter"><code>newvalue</code></em> <code class="type">integer</code> )265 → <code class="returnvalue">bytea</code>266 </p>267 <p>268 Sets <a class="link" href="functions-binarystring.html#FUNCTIONS-ZEROBASED-NOTE">n'th</a> bit in269 binary string to <em class="parameter"><code>newvalue</code></em>.270 </p>271 <p>272 <code class="literal">set_bit('\x1234567890'::bytea, 30, 0)</code>273 → <code class="returnvalue">\x1234563890</code>274 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">275 <a id="id-1.5.8.11.7.2.2.8.1.1.1" class="indexterm"></a>276 <code class="function">set_byte</code> ( <em class="parameter"><code>bytes</code></em> <code class="type">bytea</code>,277 <em class="parameter"><code>n</code></em> <code class="type">integer</code>,278 <em class="parameter"><code>newvalue</code></em> <code class="type">integer</code> )279 → <code class="returnvalue">bytea</code>280 </p>281 <p>282 Sets <a class="link" href="functions-binarystring.html#FUNCTIONS-ZEROBASED-NOTE">n'th</a> byte in283 binary string to <em class="parameter"><code>newvalue</code></em>.284 </p>285 <p>286 <code class="literal">set_byte('\x1234567890'::bytea, 4, 64)</code>287 → <code class="returnvalue">\x1234567840</code>288 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">289 <a id="id-1.5.8.11.7.2.2.9.1.1.1" class="indexterm"></a>290 <code class="function">sha224</code> ( <code class="type">bytea</code> )291 → <code class="returnvalue">bytea</code>292 </p>293 <p>294 Computes the SHA-224 <a class="link" href="functions-binarystring.html#FUNCTIONS-HASH-NOTE">hash</a>295 of the binary string.296 </p>297 <p>298 <code class="literal">sha224('abc'::bytea)</code>299 → <code class="returnvalue">\x23097d223405d8228642a477bda255b32aadbce4bda0b3f7e36c9da7</code>300 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">301 <a id="id-1.5.8.11.7.2.2.10.1.1.1" class="indexterm"></a>302 <code class="function">sha256</code> ( <code class="type">bytea</code> )303 → <code class="returnvalue">bytea</code>304 </p>305 <p>306 Computes the SHA-256 <a class="link" href="functions-binarystring.html#FUNCTIONS-HASH-NOTE">hash</a>307 of the binary string.308 </p>309 <p>310 <code class="literal">sha256('abc'::bytea)</code>311 → <code class="returnvalue">\xba7816bf8f01cfea414140de5dae2223b00361a396177a9cb410ff61f20015ad</code>312 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">313 <a id="id-1.5.8.11.7.2.2.11.1.1.1" class="indexterm"></a>314 <code class="function">sha384</code> ( <code class="type">bytea</code> )315 → <code class="returnvalue">bytea</code>316 </p>317 <p>318 Computes the SHA-384 <a class="link" href="functions-binarystring.html#FUNCTIONS-HASH-NOTE">hash</a>319 of the binary string.320 </p>321 <p>322 <code class="literal">sha384('abc'::bytea)</code>323 → <code class="returnvalue">\xcb00753f45a35e8bb5a03d699ac65007272c32ab0eded1631a8b605a43ff5bed8086072ba1e7cc2358baeca134c825a7</code>324 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">325 <a id="id-1.5.8.11.7.2.2.12.1.1.1" class="indexterm"></a>326 <code class="function">sha512</code> ( <code class="type">bytea</code> )327 → <code class="returnvalue">bytea</code>328 </p>329 <p>330 Computes the SHA-512 <a class="link" href="functions-binarystring.html#FUNCTIONS-HASH-NOTE">hash</a>331 of the binary string.332 </p>333 <p>334 <code class="literal">sha512('abc'::bytea)</code>335 → <code class="returnvalue">\xddaf35a193617abacc417349ae20413112e6fa4e89a97ea20a9eeee64b55d39a2192992a274fc1a836ba3c23a3feebbd454d4423643ce80e2a9ac94fa54ca49f</code>336 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">337 <a id="id-1.5.8.11.7.2.2.13.1.1.1" class="indexterm"></a>338 <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>] )339 → <code class="returnvalue">bytea</code>340 </p>341 <p>342 Extracts the substring of <em class="parameter"><code>bytes</code></em> starting at343 the <em class="parameter"><code>start</code></em>'th byte,344 and extending for <em class="parameter"><code>count</code></em> bytes if that is345 specified. (Same346 as <code class="literal">substring(<em class="parameter"><code>bytes</code></em>347 from <em class="parameter"><code>start</code></em>348 for <em class="parameter"><code>count</code></em>)</code>.)349 </p>350 <p>351 <code class="literal">substr('\x1234567890'::bytea, 3, 2)</code>352 → <code class="returnvalue">\x5678</code>353 </p></td></tr></tbody></table></div></div><br class="table-break" /><p id="FUNCTIONS-ZEROBASED-NOTE">354 Functions <code class="function">get_byte</code> and <code class="function">set_byte</code>355 number the first byte of a binary string as byte 0.356 Functions <code class="function">get_bit</code> and <code class="function">set_bit</code>357 number bits from the right within each byte; for example bit 0 is the least358 significant bit of the first byte, and bit 15 is the most significant bit359 of the second byte.360 </p><p id="FUNCTIONS-HASH-NOTE">361 For historical reasons, the function <code class="function">md5</code>362 returns a hex-encoded value of type <code class="type">text</code> whereas the SHA-2363 functions return type <code class="type">bytea</code>. Use the functions364 <a class="link" href="functions-binarystring.html#FUNCTION-ENCODE"><code class="function">encode</code></a>365 and <a class="link" href="functions-binarystring.html#FUNCTION-DECODE"><code class="function">decode</code></a> to366 convert between the two. For example write <code class="literal">encode(sha256('abc'),367 'hex')</code> to get a hex-encoded text representation,368 or <code class="literal">decode(md5('abc'), 'hex')</code> to get369 a <code class="type">bytea</code> value.370 </p><p>371 <a id="id-1.5.8.11.10.1" class="indexterm"></a>372 <a id="id-1.5.8.11.10.2" class="indexterm"></a>373 Functions for converting strings between different character sets374 (encodings), and for representing arbitrary binary data in textual375 form, are shown in376 <a class="xref" href="functions-binarystring.html#FUNCTIONS-BINARYSTRING-CONVERSIONS" title="Table 9.13. Text/Binary String Conversion Functions">Table 9.13</a>. For these377 functions, an argument or result of type <code class="type">text</code> is expressed378 in the database's default encoding, while arguments or results of379 type <code class="type">bytea</code> are in an encoding named by another argument.380 </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">381 Function382 </p>383 <p>384 Description385 </p>386 <p>387 Example(s)388 </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">389 <a id="id-1.5.8.11.11.2.2.1.1.1.1" class="indexterm"></a>390 <code class="function">convert</code> ( <em class="parameter"><code>bytes</code></em> <code class="type">bytea</code>,391 <em class="parameter"><code>src_encoding</code></em> <code class="type">name</code>,392 <em class="parameter"><code>dest_encoding</code></em> <code class="type">name</code> )393 → <code class="returnvalue">bytea</code>394 </p>395 <p>396 Converts a binary string representing text in397 encoding <em class="parameter"><code>src_encoding</code></em>398 to a binary string in encoding <em class="parameter"><code>dest_encoding</code></em>399 (see <a class="xref" href="multibyte.html#MULTIBYTE-CONVERSIONS-SUPPORTED" title="24.3.4. Available Character Set Conversions">Section 24.3.4</a> for400 available conversions).401 </p>402 <p>403 <code class="literal">convert('text_in_utf8', 'UTF8', 'LATIN1')</code>404 → <code class="returnvalue">\x746578745f696e5f75746638</code>405 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">406 <a id="id-1.5.8.11.11.2.2.2.1.1.1" class="indexterm"></a>407 <code class="function">convert_from</code> ( <em class="parameter"><code>bytes</code></em> <code class="type">bytea</code>,408 <em class="parameter"><code>src_encoding</code></em> <code class="type">name</code> )409 → <code class="returnvalue">text</code>410 </p>411 <p>412 Converts a binary string representing text in413 encoding <em class="parameter"><code>src_encoding</code></em>414 to <code class="type">text</code> in the database encoding415 (see <a class="xref" href="multibyte.html#MULTIBYTE-CONVERSIONS-SUPPORTED" title="24.3.4. Available Character Set Conversions">Section 24.3.4</a> for416 available conversions).417 </p>418 <p>419 <code class="literal">convert_from('text_in_utf8', 'UTF8')</code>420 → <code class="returnvalue">text_in_utf8</code>421 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">422 <a id="id-1.5.8.11.11.2.2.3.1.1.1" class="indexterm"></a>423 <code class="function">convert_to</code> ( <em class="parameter"><code>string</code></em> <code class="type">text</code>,424 <em class="parameter"><code>dest_encoding</code></em> <code class="type">name</code> )425 → <code class="returnvalue">bytea</code>426 </p>427 <p>428 Converts a <code class="type">text</code> string (in the database encoding) to a429 binary string encoded in encoding <em class="parameter"><code>dest_encoding</code></em>430 (see <a class="xref" href="multibyte.html#MULTIBYTE-CONVERSIONS-SUPPORTED" title="24.3.4. Available Character Set Conversions">Section 24.3.4</a> for431 available conversions).432 </p>433 <p>434 <code class="literal">convert_to('some_text', 'UTF8')</code>435 → <code class="returnvalue">\x736f6d655f74657874</code>436 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">437 <a id="FUNCTION-ENCODE" class="indexterm"></a>438 <code class="function">encode</code> ( <em class="parameter"><code>bytes</code></em> <code class="type">bytea</code>,439 <em class="parameter"><code>format</code></em> <code class="type">text</code> )440 → <code class="returnvalue">text</code>441 </p>442 <p>443 Encodes binary data into a textual representation; supported444 <em class="parameter"><code>format</code></em> values are:445 <a class="link" href="functions-binarystring.html#ENCODE-FORMAT-BASE64"><code class="literal">base64</code></a>,446 <a class="link" href="functions-binarystring.html#ENCODE-FORMAT-ESCAPE"><code class="literal">escape</code></a>,447 <a class="link" href="functions-binarystring.html#ENCODE-FORMAT-HEX"><code class="literal">hex</code></a>.448 </p>449 <p>450 <code class="literal">encode('123\000\001', 'base64')</code>451 → <code class="returnvalue">MTIzAAE=</code>452 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">453 <a id="FUNCTION-DECODE" class="indexterm"></a>454 <code class="function">decode</code> ( <em class="parameter"><code>string</code></em> <code class="type">text</code>,455 <em class="parameter"><code>format</code></em> <code class="type">text</code> )456 → <code class="returnvalue">bytea</code>457 </p>458 <p>459 Decodes binary data from a textual representation; supported460 <em class="parameter"><code>format</code></em> values are the same as461 for <code class="function">encode</code>.462 </p>463 <p>464 <code class="literal">decode('MTIzAAE=', 'base64')</code>465 → <code class="returnvalue">\x3132330001</code>466 </p></td></tr></tbody></table></div></div><br class="table-break" /><p>467 The <code class="function">encode</code> and <code class="function">decode</code>468 functions support the following textual formats:469 470 </p><div class="variablelist"><dl class="variablelist"><dt id="ENCODE-FORMAT-BASE64"><span class="term">base64471 <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>472 The <code class="literal">base64</code> format is that473 of <a class="ulink" href="https://datatracker.ietf.org/doc/html/rfc2045#section-6.8" target="_top">RFC474 2045 Section 6.8</a>. As per the <acronym class="acronym">RFC</acronym>, encoded lines are475 broken at 76 characters. However instead of the MIME CRLF476 end-of-line marker, only a newline is used for end-of-line.477 The <code class="function">decode</code> function ignores carriage-return,478 newline, space, and tab characters. Otherwise, an error is479 raised when <code class="function">decode</code> is supplied invalid480 base64 data — including when trailing padding is incorrect.481 </p></dd><dt id="ENCODE-FORMAT-ESCAPE"><span class="term">escape482 <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>483 The <code class="literal">escape</code> format converts zero bytes and484 bytes with the high bit set into octal escape sequences485 (<code class="literal">\</code><em class="replaceable"><code>nnn</code></em>), and it doubles486 backslashes. Other byte values are represented literally.487 The <code class="function">decode</code> function will raise an error if a488 backslash is not followed by either a second backslash or three489 octal digits; it accepts other byte values unchanged.490 </p></dd><dt id="ENCODE-FORMAT-HEX"><span class="term">hex491 <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>492 The <code class="literal">hex</code> format represents each 4 bits of493 data as one hexadecimal digit, <code class="literal">0</code>494 through <code class="literal">f</code>, writing the higher-order digit of495 each byte first. The <code class="function">encode</code> function outputs496 the <code class="literal">a</code>-<code class="literal">f</code> hex digits in lower497 case. Because the smallest unit of data is 8 bits, there are498 always an even number of characters returned499 by <code class="function">encode</code>.500 The <code class="function">decode</code> function501 accepts the <code class="literal">a</code>-<code class="literal">f</code> characters in502 either upper or lower case. An error is raised503 when <code class="function">decode</code> is given invalid hex data504 — including when given an odd number of characters.505 </p></dd></dl></div><p>506 </p><p>507 See also the aggregate function <code class="function">string_agg</code> in508 <a class="xref" href="functions-aggregate.html" title="9.21. Aggregate Functions">Section 9.21</a> and the large object functions509 in <a class="xref" href="lo-funcs.html" title="35.4. Server-Side Functions">Section 35.4</a>.510 </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 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 9.6. Bit String Functions and Operators</td></tr></table></div></body></html>