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>F.18. hstore — hstore key/value datatype</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="fuzzystrmatch.html" title="F.17. fuzzystrmatch — determine string similarities and distance" /><link rel="next" href="intagg.html" title="F.19. intagg — integer aggregator and enumerator" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">F.18. hstore — hstore key/value datatype</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="fuzzystrmatch.html" title="F.17. fuzzystrmatch — determine string similarities and distance">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="contrib.html" title="Appendix F. Additional Supplied Modules and Extensions">Up</a></td><th width="60%" align="center">Appendix F. Additional Supplied Modules and Extensions</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="intagg.html" title="F.19. intagg — integer aggregator and enumerator">Next</a></td></tr></table><hr /></div><div class="sect1" id="HSTORE"><div class="titlepage"><div><div><h2 class="title" style="clear: both">F.18. hstore — hstore key/value datatype <a href="#HSTORE" class="id_link">#</a></h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="hstore.html#HSTORE-EXTERNAL-REP">F.18.1. <code class="type">hstore</code> External Representation</a></span></dt><dt><span class="sect2"><a href="hstore.html#HSTORE-OPS-FUNCS">F.18.2. <code class="type">hstore</code> Operators and Functions</a></span></dt><dt><span class="sect2"><a href="hstore.html#HSTORE-INDEXES">F.18.3. Indexes</a></span></dt><dt><span class="sect2"><a href="hstore.html#HSTORE-EXAMPLES">F.18.4. Examples</a></span></dt><dt><span class="sect2"><a href="hstore.html#HSTORE-STATISTICS">F.18.5. Statistics</a></span></dt><dt><span class="sect2"><a href="hstore.html#HSTORE-COMPATIBILITY">F.18.6. Compatibility</a></span></dt><dt><span class="sect2"><a href="hstore.html#HSTORE-TRANSFORMS">F.18.7. Transforms</a></span></dt><dt><span class="sect2"><a href="hstore.html#HSTORE-AUTHORS">F.18.8. Authors</a></span></dt></dl></div><a id="id-1.11.7.28.2" class="indexterm"></a><p>3 This module implements the <code class="type">hstore</code> data type for storing sets of4 key/value pairs within a single <span class="productname">PostgreSQL</span> value.5 This can be useful in various scenarios, such as rows with many attributes6 that are rarely examined, or semi-structured data. Keys and values are7 simply text strings.8 </p><p>9 This module is considered <span class="quote">“<span class="quote">trusted</span>”</span>, that is, it can be10 installed by non-superusers who have <code class="literal">CREATE</code> privilege11 on the current database.12 </p><div class="sect2" id="HSTORE-EXTERNAL-REP"><div class="titlepage"><div><div><h3 class="title">F.18.1. <code class="type">hstore</code> External Representation <a href="#HSTORE-EXTERNAL-REP" class="id_link">#</a></h3></div></div></div><p>13 14 The text representation of an <code class="type">hstore</code>, used for input and output,15 includes zero or more <em class="replaceable"><code>key</code></em> <code class="literal">=></code>16 <em class="replaceable"><code>value</code></em> pairs separated by commas. Some examples:17 18</p><pre class="synopsis">19k => v20foo => bar, baz => whatever21"1-a" => "anything at all"22</pre><p>23 24 The order of the pairs is not significant (and may not be reproduced on25 output). Whitespace between pairs or around the <code class="literal">=></code> sign is26 ignored. Double-quote keys and values that include whitespace, commas,27 <code class="literal">=</code>s or <code class="literal">></code>s. To include a double quote or a28 backslash in a key or value, escape it with a backslash.29 </p><p>30 Each key in an <code class="type">hstore</code> is unique. If you declare an <code class="type">hstore</code>31 with duplicate keys, only one will be stored in the <code class="type">hstore</code> and32 there is no guarantee as to which will be kept:33 34</p><pre class="programlisting">35SELECT 'a=>1,a=>2'::hstore;36 hstore37----------38 "a"=>"1"39</pre><p>40 </p><p>41 A value (but not a key) can be an SQL <code class="literal">NULL</code>. For example:42 43</p><pre class="programlisting">44key => NULL45</pre><p>46 47 The <code class="literal">NULL</code> keyword is case-insensitive. Double-quote the48 <code class="literal">NULL</code> to treat it as the ordinary string <span class="quote">“<span class="quote">NULL</span>”</span>.49 </p><div class="note"><h3 class="title">Note</h3><p>50 Keep in mind that the <code class="type">hstore</code> text format, when used for input,51 applies <span class="emphasis"><em>before</em></span> any required quoting or escaping. If you are52 passing an <code class="type">hstore</code> literal via a parameter, then no additional53 processing is needed. But if you're passing it as a quoted literal54 constant, then any single-quote characters and (depending on the setting of55 the <code class="varname">standard_conforming_strings</code> configuration parameter)56 backslash characters need to be escaped correctly. See57 <a class="xref" href="sql-syntax-lexical.html#SQL-SYNTAX-STRINGS" title="4.1.2.1. String Constants">Section 4.1.2.1</a> for more on the handling of string58 constants.59 </p></div><p>60 On output, double quotes always surround keys and values, even when it's61 not strictly necessary.62 </p></div><div class="sect2" id="HSTORE-OPS-FUNCS"><div class="titlepage"><div><div><h3 class="title">F.18.2. <code class="type">hstore</code> Operators and Functions <a href="#HSTORE-OPS-FUNCS" class="id_link">#</a></h3></div></div></div><p>63 The operators provided by the <code class="literal">hstore</code> module are64 shown in <a class="xref" href="hstore.html#HSTORE-OP-TABLE" title="Table F.7. hstore Operators">Table F.7</a>, the functions65 in <a class="xref" href="hstore.html#HSTORE-FUNC-TABLE" title="Table F.8. hstore Functions">Table F.8</a>.66 </p><div class="table" id="HSTORE-OP-TABLE"><p class="title"><strong>Table F.7. <code class="type">hstore</code> Operators</strong></p><div class="table-contents"><table class="table" summary="hstore Operators" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">67 Operator68 </p>69 <p>70 Description71 </p>72 <p>73 Example(s)74 </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">75 <code class="type">hstore</code> <code class="literal">-></code> <code class="type">text</code>76 → <code class="returnvalue">text</code>77 </p>78 <p>79 Returns value associated with given key, or <code class="literal">NULL</code> if80 not present.81 </p>82 <p>83 <code class="literal">'a=>x, b=>y'::hstore -> 'a'</code>84 → <code class="returnvalue">x</code>85 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">86 <code class="type">hstore</code> <code class="literal">-></code> <code class="type">text[]</code>87 → <code class="returnvalue">text[]</code>88 </p>89 <p>90 Returns values associated with given keys, or <code class="literal">NULL</code>91 if not present.92 </p>93 <p>94 <code class="literal">'a=>x, b=>y, c=>z'::hstore -> ARRAY['c','a']</code>95 → <code class="returnvalue">{"z","x"}</code>96 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">97 <code class="type">hstore</code> <code class="literal">||</code> <code class="type">hstore</code>98 → <code class="returnvalue">hstore</code>99 </p>100 <p>101 Concatenates two <code class="type">hstore</code>s.102 </p>103 <p>104 <code class="literal">'a=>b, c=>d'::hstore || 'c=>x, d=>q'::hstore</code>105 → <code class="returnvalue">"a"=>"b", "c"=>"x", "d"=>"q"</code>106 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">107 <code class="type">hstore</code> <code class="literal">?</code> <code class="type">text</code>108 → <code class="returnvalue">boolean</code>109 </p>110 <p>111 Does <code class="type">hstore</code> contain key?112 </p>113 <p>114 <code class="literal">'a=>1'::hstore ? 'a'</code>115 → <code class="returnvalue">t</code>116 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">117 <code class="type">hstore</code> <code class="literal">?&</code> <code class="type">text[]</code>118 → <code class="returnvalue">boolean</code>119 </p>120 <p>121 Does <code class="type">hstore</code> contain all the specified keys?122 </p>123 <p>124 <code class="literal">'a=>1,b=>2'::hstore ?& ARRAY['a','b']</code>125 → <code class="returnvalue">t</code>126 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">127 <code class="type">hstore</code> <code class="literal">?|</code> <code class="type">text[]</code>128 → <code class="returnvalue">boolean</code>129 </p>130 <p>131 Does <code class="type">hstore</code> contain any of the specified keys?132 </p>133 <p>134 <code class="literal">'a=>1,b=>2'::hstore ?| ARRAY['b','c']</code>135 → <code class="returnvalue">t</code>136 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">137 <code class="type">hstore</code> <code class="literal">@></code> <code class="type">hstore</code>138 → <code class="returnvalue">boolean</code>139 </p>140 <p>141 Does left operand contain right?142 </p>143 <p>144 <code class="literal">'a=>b, b=>1, c=>NULL'::hstore @> 'b=>1'</code>145 → <code class="returnvalue">t</code>146 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">147 <code class="type">hstore</code> <code class="literal"><@</code> <code class="type">hstore</code>148 → <code class="returnvalue">boolean</code>149 </p>150 <p>151 Is left operand contained in right?152 </p>153 <p>154 <code class="literal">'a=>c'::hstore <@ 'a=>b, b=>1, c=>NULL'</code>155 → <code class="returnvalue">f</code>156 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">157 <code class="type">hstore</code> <code class="literal">-</code> <code class="type">text</code>158 → <code class="returnvalue">hstore</code>159 </p>160 <p>161 Deletes key from left operand.162 </p>163 <p>164 <code class="literal">'a=>1, b=>2, c=>3'::hstore - 'b'::text</code>165 → <code class="returnvalue">"a"=>"1", "c"=>"3"</code>166 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">167 <code class="type">hstore</code> <code class="literal">-</code> <code class="type">text[]</code>168 → <code class="returnvalue">hstore</code>169 </p>170 <p>171 Deletes keys from left operand.172 </p>173 <p>174 <code class="literal">'a=>1, b=>2, c=>3'::hstore - ARRAY['a','b']</code>175 → <code class="returnvalue">"c"=>"3"</code>176 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">177 <code class="type">hstore</code> <code class="literal">-</code> <code class="type">hstore</code>178 → <code class="returnvalue">hstore</code>179 </p>180 <p>181 Deletes pairs from left operand that match pairs in the right operand.182 </p>183 <p>184 <code class="literal">'a=>1, b=>2, c=>3'::hstore - 'a=>4, b=>2'::hstore</code>185 → <code class="returnvalue">"a"=>"1", "c"=>"3"</code>186 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">187 <code class="type">anyelement</code> <code class="literal">#=</code> <code class="type">hstore</code>188 → <code class="returnvalue">anyelement</code>189 </p>190 <p>191 Replaces fields in the left operand (which must be a composite type)192 with matching values from <code class="type">hstore</code>.193 </p>194 <p>195 <code class="literal">ROW(1,3) #= 'f1=>11'::hstore</code>196 → <code class="returnvalue">(11,3)</code>197 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">198 <code class="literal">%%</code> <code class="type">hstore</code>199 → <code class="returnvalue">text[]</code>200 </p>201 <p>202 Converts <code class="type">hstore</code> to an array of alternating keys and203 values.204 </p>205 <p>206 <code class="literal">%% 'a=>foo, b=>bar'::hstore</code>207 → <code class="returnvalue">{a,foo,b,bar}</code>208 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">209 <code class="literal">%#</code> <code class="type">hstore</code>210 → <code class="returnvalue">text[]</code>211 </p>212 <p>213 Converts <code class="type">hstore</code> to a two-dimensional key/value array.214 </p>215 <p>216 <code class="literal">%# 'a=>foo, b=>bar'::hstore</code>217 → <code class="returnvalue">{{a,foo},{b,bar}}</code>218 </p></td></tr></tbody></table></div></div><br class="table-break" /><div class="table" id="HSTORE-FUNC-TABLE"><p class="title"><strong>Table F.8. <code class="type">hstore</code> Functions</strong></p><div class="table-contents"><table class="table" summary="hstore Functions" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">219 Function220 </p>221 <p>222 Description223 </p>224 <p>225 Example(s)226 </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">227 <a id="id-1.11.7.28.6.4.2.2.1.1.1.1" class="indexterm"></a>228 <code class="function">hstore</code> ( <code class="type">record</code> )229 → <code class="returnvalue">hstore</code>230 </p>231 <p>232 Constructs an <code class="type">hstore</code> from a record or row.233 </p>234 <p>235 <code class="literal">hstore(ROW(1,2))</code>236 → <code class="returnvalue">"f1"=>"1", "f2"=>"2"</code>237 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">238 <code class="function">hstore</code> ( <code class="type">text[]</code> )239 → <code class="returnvalue">hstore</code>240 </p>241 <p>242 Constructs an <code class="type">hstore</code> from an array, which may be either243 a key/value array, or a two-dimensional array.244 </p>245 <p>246 <code class="literal">hstore(ARRAY['a','1','b','2'])</code>247 → <code class="returnvalue">"a"=>"1", "b"=>"2"</code>248 </p>249 <p>250 <code class="literal">hstore(ARRAY[['c','3'],['d','4']])</code>251 → <code class="returnvalue">"c"=>"3", "d"=>"4"</code>252 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">253 <code class="function">hstore</code> ( <code class="type">text[]</code>, <code class="type">text[]</code> )254 → <code class="returnvalue">hstore</code>255 </p>256 <p>257 Constructs an <code class="type">hstore</code> from separate key and value arrays.258 </p>259 <p>260 <code class="literal">hstore(ARRAY['a','b'], ARRAY['1','2'])</code>261 → <code class="returnvalue">"a"=>"1", "b"=>"2"</code>262 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">263 <code class="function">hstore</code> ( <code class="type">text</code>, <code class="type">text</code> )264 → <code class="returnvalue">hstore</code>265 </p>266 <p>267 Makes a single-item <code class="type">hstore</code>.268 </p>269 <p>270 <code class="literal">hstore('a', 'b')</code>271 → <code class="returnvalue">"a"=>"b"</code>272 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">273 <a id="id-1.11.7.28.6.4.2.2.5.1.1.1" class="indexterm"></a>274 <code class="function">akeys</code> ( <code class="type">hstore</code> )275 → <code class="returnvalue">text[]</code>276 </p>277 <p>278 Extracts an <code class="type">hstore</code>'s keys as an array.279 </p>280 <p>281 <code class="literal">akeys('a=>1,b=>2')</code>282 → <code class="returnvalue">{a,b}</code>283 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">284 <a id="id-1.11.7.28.6.4.2.2.6.1.1.1" class="indexterm"></a>285 <code class="function">skeys</code> ( <code class="type">hstore</code> )286 → <code class="returnvalue">setof text</code>287 </p>288 <p>289 Extracts an <code class="type">hstore</code>'s keys as a set.290 </p>291 <p>292 <code class="literal">skeys('a=>1,b=>2')</code>293 → <code class="returnvalue"></code>294</p><pre class="programlisting">295a296b297</pre><p>298 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">299 <a id="id-1.11.7.28.6.4.2.2.7.1.1.1" class="indexterm"></a>300 <code class="function">avals</code> ( <code class="type">hstore</code> )301 → <code class="returnvalue">text[]</code>302 </p>303 <p>304 Extracts an <code class="type">hstore</code>'s values as an array.305 </p>306 <p>307 <code class="literal">avals('a=>1,b=>2')</code>308 → <code class="returnvalue">{1,2}</code>309 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">310 <a id="id-1.11.7.28.6.4.2.2.8.1.1.1" class="indexterm"></a>311 <code class="function">svals</code> ( <code class="type">hstore</code> )312 → <code class="returnvalue">setof text</code>313 </p>314 <p>315 Extracts an <code class="type">hstore</code>'s values as a set.316 </p>317 <p>318 <code class="literal">svals('a=>1,b=>2')</code>319 → <code class="returnvalue"></code>320</p><pre class="programlisting">32113222323</pre><p>324 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">325 <a id="id-1.11.7.28.6.4.2.2.9.1.1.1" class="indexterm"></a>326 <code class="function">hstore_to_array</code> ( <code class="type">hstore</code> )327 → <code class="returnvalue">text[]</code>328 </p>329 <p>330 Extracts an <code class="type">hstore</code>'s keys and values as an array of331 alternating keys and values.332 </p>333 <p>334 <code class="literal">hstore_to_array('a=>1,b=>2')</code>335 → <code class="returnvalue">{a,1,b,2}</code>336 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">337 <a id="id-1.11.7.28.6.4.2.2.10.1.1.1" class="indexterm"></a>338 <code class="function">hstore_to_matrix</code> ( <code class="type">hstore</code> )339 → <code class="returnvalue">text[]</code>340 </p>341 <p>342 Extracts an <code class="type">hstore</code>'s keys and values as a two-dimensional343 array.344 </p>345 <p>346 <code class="literal">hstore_to_matrix('a=>1,b=>2')</code>347 → <code class="returnvalue">{{a,1},{b,2}}</code>348 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">349 <a id="id-1.11.7.28.6.4.2.2.11.1.1.1" class="indexterm"></a>350 <code class="function">hstore_to_json</code> ( <code class="type">hstore</code> )351 → <code class="returnvalue">json</code>352 </p>353 <p>354 Converts an <code class="type">hstore</code> to a <code class="type">json</code> value,355 converting all non-null values to JSON strings.356 </p>357 <p>358 This function is used implicitly when an <code class="type">hstore</code> value is359 cast to <code class="type">json</code>.360 </p>361 <p>362 <code class="literal">hstore_to_json('"a key"=>1, b=>t, c=>null, d=>12345, e=>012345, f=>1.234, g=>2.345e+4')</code>363 → <code class="returnvalue">{"a key": "1", "b": "t", "c": null, "d": "12345", "e": "012345", "f": "1.234", "g": "2.345e+4"}</code>364 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">365 <a id="id-1.11.7.28.6.4.2.2.12.1.1.1" class="indexterm"></a>366 <code class="function">hstore_to_jsonb</code> ( <code class="type">hstore</code> )367 → <code class="returnvalue">jsonb</code>368 </p>369 <p>370 Converts an <code class="type">hstore</code> to a <code class="type">jsonb</code> value,371 converting all non-null values to JSON strings.372 </p>373 <p>374 This function is used implicitly when an <code class="type">hstore</code> value is375 cast to <code class="type">jsonb</code>.376 </p>377 <p>378 <code class="literal">hstore_to_jsonb('"a key"=>1, b=>t, c=>null, d=>12345, e=>012345, f=>1.234, g=>2.345e+4')</code>379 → <code class="returnvalue">{"a key": "1", "b": "t", "c": null, "d": "12345", "e": "012345", "f": "1.234", "g": "2.345e+4"}</code>380 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">381 <a id="id-1.11.7.28.6.4.2.2.13.1.1.1" class="indexterm"></a>382 <code class="function">hstore_to_json_loose</code> ( <code class="type">hstore</code> )383 → <code class="returnvalue">json</code>384 </p>385 <p>386 Converts an <code class="type">hstore</code> to a <code class="type">json</code> value, but387 attempts to distinguish numerical and Boolean values so they are388 unquoted in the JSON.389 </p>390 <p>391 <code class="literal">hstore_to_json_loose('"a key"=>1, b=>t, c=>null, d=>12345, e=>012345, f=>1.234, g=>2.345e+4')</code>392 → <code class="returnvalue">{"a key": 1, "b": true, "c": null, "d": 12345, "e": "012345", "f": 1.234, "g": 2.345e+4}</code>393 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">394 <a id="id-1.11.7.28.6.4.2.2.14.1.1.1" class="indexterm"></a>395 <code class="function">hstore_to_jsonb_loose</code> ( <code class="type">hstore</code> )396 → <code class="returnvalue">jsonb</code>397 </p>398 <p>399 Converts an <code class="type">hstore</code> to a <code class="type">jsonb</code> value, but400 attempts to distinguish numerical and Boolean values so they are401 unquoted in the JSON.402 </p>403 <p>404 <code class="literal">hstore_to_jsonb_loose('"a key"=>1, b=>t, c=>null, d=>12345, e=>012345, f=>1.234, g=>2.345e+4')</code>405 → <code class="returnvalue">{"a key": 1, "b": true, "c": null, "d": 12345, "e": "012345", "f": 1.234, "g": 2.345e+4}</code>406 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">407 <a id="id-1.11.7.28.6.4.2.2.15.1.1.1" class="indexterm"></a>408 <code class="function">slice</code> ( <code class="type">hstore</code>, <code class="type">text[]</code> )409 → <code class="returnvalue">hstore</code>410 </p>411 <p>412 Extracts a subset of an <code class="type">hstore</code> containing only the413 specified keys.414 </p>415 <p>416 <code class="literal">slice('a=>1,b=>2,c=>3'::hstore, ARRAY['b','c','x'])</code>417 → <code class="returnvalue">"b"=>"2", "c"=>"3"</code>418 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">419 <a id="id-1.11.7.28.6.4.2.2.16.1.1.1" class="indexterm"></a>420 <code class="function">each</code> ( <code class="type">hstore</code> )421 → <code class="returnvalue">setof record</code>422 ( <em class="parameter"><code>key</code></em> <code class="type">text</code>,423 <em class="parameter"><code>value</code></em> <code class="type">text</code> )424 </p>425 <p>426 Extracts an <code class="type">hstore</code>'s keys and values as a set of records.427 </p>428 <p>429 <code class="literal">select * from each('a=>1,b=>2')</code>430 → <code class="returnvalue"></code>431</p><pre class="programlisting">432 key | value433-----+-------434 a | 1435 b | 2436</pre><p>437 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">438 <a id="id-1.11.7.28.6.4.2.2.17.1.1.1" class="indexterm"></a>439 <code class="function">exist</code> ( <code class="type">hstore</code>, <code class="type">text</code> )440 → <code class="returnvalue">boolean</code>441 </p>442 <p>443 Does <code class="type">hstore</code> contain key?444 </p>445 <p>446 <code class="literal">exist('a=>1', 'a')</code>447 → <code class="returnvalue">t</code>448 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">449 <a id="id-1.11.7.28.6.4.2.2.18.1.1.1" class="indexterm"></a>450 <code class="function">defined</code> ( <code class="type">hstore</code>, <code class="type">text</code> )451 → <code class="returnvalue">boolean</code>452 </p>453 <p>454 Does <code class="type">hstore</code> contain a non-<code class="literal">NULL</code> value455 for key?456 </p>457 <p>458 <code class="literal">defined('a=>NULL', 'a')</code>459 → <code class="returnvalue">f</code>460 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">461 <a id="id-1.11.7.28.6.4.2.2.19.1.1.1" class="indexterm"></a>462 <code class="function">delete</code> ( <code class="type">hstore</code>, <code class="type">text</code> )463 → <code class="returnvalue">hstore</code>464 </p>465 <p>466 Deletes pair with matching key.467 </p>468 <p>469 <code class="literal">delete('a=>1,b=>2', 'b')</code>470 → <code class="returnvalue">"a"=>"1"</code>471 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">472 <code class="function">delete</code> ( <code class="type">hstore</code>, <code class="type">text[]</code> )473 → <code class="returnvalue">hstore</code>474 </p>475 <p>476 Deletes pairs with matching keys.477 </p>478 <p>479 <code class="literal">delete('a=>1,b=>2,c=>3', ARRAY['a','b'])</code>480 → <code class="returnvalue">"c"=>"3"</code>481 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">482 <code class="function">delete</code> ( <code class="type">hstore</code>, <code class="type">hstore</code> )483 → <code class="returnvalue">hstore</code>484 </p>485 <p>486 Deletes pairs matching those in the second argument.487 </p>488 <p>489 <code class="literal">delete('a=>1,b=>2', 'a=>4,b=>2'::hstore)</code>490 → <code class="returnvalue">"a"=>"1"</code>491 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">492 <a id="id-1.11.7.28.6.4.2.2.22.1.1.1" class="indexterm"></a>493 <code class="function">populate_record</code> ( <code class="type">anyelement</code>, <code class="type">hstore</code> )494 → <code class="returnvalue">anyelement</code>495 </p>496 <p>497 Replaces fields in the left operand (which must be a composite type)498 with matching values from <code class="type">hstore</code>.499 </p>500 <p>501 <code class="literal">populate_record(ROW(1,2), 'f1=>42'::hstore)</code>502 → <code class="returnvalue">(42,2)</code>503 </p></td></tr></tbody></table></div></div><br class="table-break" /><p>504 In addition to these operators and functions, values of505 the <code class="type">hstore</code> type can be subscripted, allowing them to act506 like associative arrays. Only a single subscript of type <code class="type">text</code>507 can be specified; it is interpreted as a key and the corresponding508 value is fetched or stored. For example,509 510</p><pre class="programlisting">511CREATE TABLE mytable (h hstore);512INSERT INTO mytable VALUES ('a=>b, c=>d');513SELECT h['a'] FROM mytable;514 h515---516 b517(1 row)518 519UPDATE mytable SET h['c'] = 'new';520SELECT h FROM mytable;521 h522----------------------523 "a"=>"b", "c"=>"new"524(1 row)525</pre><p>526 527 A subscripted fetch returns <code class="literal">NULL</code> if the subscript528 is <code class="literal">NULL</code> or that key does not exist in529 the <code class="type">hstore</code>. (Thus, a subscripted fetch is not greatly530 different from the <code class="literal">-></code> operator.)531 A subscripted update fails if the subscript is <code class="literal">NULL</code>;532 otherwise, it replaces the value for that key, adding an entry to533 the <code class="type">hstore</code> if the key does not already exist.534 </p></div><div class="sect2" id="HSTORE-INDEXES"><div class="titlepage"><div><div><h3 class="title">F.18.3. Indexes <a href="#HSTORE-INDEXES" class="id_link">#</a></h3></div></div></div><p>535 <code class="type">hstore</code> has GiST and GIN index support for the <code class="literal">@></code>,536 <code class="literal">?</code>, <code class="literal">?&</code> and <code class="literal">?|</code> operators. For example:537 </p><pre class="programlisting">538CREATE INDEX hidx ON testhstore USING GIST (h);539 540CREATE INDEX hidx ON testhstore USING GIN (h);541</pre><p>542 <code class="literal">gist_hstore_ops</code> GiST opclass approximates a set of543 key/value pairs as a bitmap signature. Its optional integer parameter544 <code class="literal">siglen</code> determines the545 signature length in bytes. The default length is 16 bytes.546 Valid values of signature length are between 1 and 2024 bytes. Longer547 signatures lead to a more precise search (scanning a smaller fraction of the index and548 fewer heap pages), at the cost of a larger index.549 </p><p>550 Example of creating such an index with a signature length of 32 bytes:551</p><pre class="programlisting">552CREATE INDEX hidx ON testhstore USING GIST (h gist_hstore_ops(siglen=32));553</pre><p>554 </p><p>555 <code class="type">hstore</code> also supports <code class="type">btree</code> or <code class="type">hash</code> indexes for556 the <code class="literal">=</code> operator. This allows <code class="type">hstore</code> columns to be557 declared <code class="literal">UNIQUE</code>, or to be used in <code class="literal">GROUP BY</code>,558 <code class="literal">ORDER BY</code> or <code class="literal">DISTINCT</code> expressions. The sort ordering559 for <code class="type">hstore</code> values is not particularly useful, but these indexes560 may be useful for equivalence lookups. Create indexes for <code class="literal">=</code>561 comparisons as follows:562 </p><pre class="programlisting">563CREATE INDEX hidx ON testhstore USING BTREE (h);564 565CREATE INDEX hidx ON testhstore USING HASH (h);566</pre></div><div class="sect2" id="HSTORE-EXAMPLES"><div class="titlepage"><div><div><h3 class="title">F.18.4. Examples <a href="#HSTORE-EXAMPLES" class="id_link">#</a></h3></div></div></div><p>567 Add a key, or update an existing key with a new value:568</p><pre class="programlisting">569UPDATE tab SET h['c'] = '3';570</pre><p>571 Another way to do the same thing is:572</p><pre class="programlisting">573UPDATE tab SET h = h || hstore('c', '3');574</pre><p>575 If multiple keys are to be added or changed in one operation,576 the concatenation approach is more efficient than subscripting:577</p><pre class="programlisting">578UPDATE tab SET h = h || hstore(array['q', 'w'], array['11', '12']);579</pre><p>580 </p><p>581 Delete a key:582</p><pre class="programlisting">583UPDATE tab SET h = delete(h, 'k1');584</pre><p>585 </p><p>586 Convert a <code class="type">record</code> to an <code class="type">hstore</code>:587</p><pre class="programlisting">588CREATE TABLE test (col1 integer, col2 text, col3 text);589INSERT INTO test VALUES (123, 'foo', 'bar');590 591SELECT hstore(t) FROM test AS t;592 hstore593---------------------------------------------594 "col1"=>"123", "col2"=>"foo", "col3"=>"bar"595(1 row)596</pre><p>597 </p><p>598 Convert an <code class="type">hstore</code> to a predefined <code class="type">record</code> type:599</p><pre class="programlisting">600CREATE TABLE test (col1 integer, col2 text, col3 text);601 602SELECT * FROM populate_record(null::test,603 '"col1"=>"456", "col2"=>"zzz"');604 col1 | col2 | col3605------+------+------606 456 | zzz |607(1 row)608</pre><p>609 </p><p>610 Modify an existing record using the values from an <code class="type">hstore</code>:611</p><pre class="programlisting">612CREATE TABLE test (col1 integer, col2 text, col3 text);613INSERT INTO test VALUES (123, 'foo', 'bar');614 615SELECT (r).* FROM (SELECT t #= '"col3"=>"baz"' AS r FROM test t) s;616 col1 | col2 | col3617------+------+------618 123 | foo | baz619(1 row)620</pre><p>621 </p></div><div class="sect2" id="HSTORE-STATISTICS"><div class="titlepage"><div><div><h3 class="title">F.18.5. Statistics <a href="#HSTORE-STATISTICS" class="id_link">#</a></h3></div></div></div><p>622 The <code class="type">hstore</code> type, because of its intrinsic liberality, could623 contain a lot of different keys. Checking for valid keys is the task of the624 application. The following examples demonstrate several techniques for625 checking keys and obtaining statistics.626 </p><p>627 Simple example:628</p><pre class="programlisting">629SELECT * FROM each('aaa=>bq, b=>NULL, ""=>1');630</pre><p>631 </p><p>632 Using a table:633</p><pre class="programlisting">634CREATE TABLE stat AS SELECT (each(h)).key, (each(h)).value FROM testhstore;635</pre><p>636 </p><p>637 Online statistics:638</p><pre class="programlisting">639SELECT key, count(*) FROM640 (SELECT (each(h)).key FROM testhstore) AS stat641 GROUP BY key642 ORDER BY count DESC, key;643 key | count644-----------+-------645 line | 883646 query | 207647 pos | 203648 node | 202649 space | 197650 status | 195651 public | 194652 title | 190653 org | 189654...................655</pre><p>656 </p></div><div class="sect2" id="HSTORE-COMPATIBILITY"><div class="titlepage"><div><div><h3 class="title">F.18.6. Compatibility <a href="#HSTORE-COMPATIBILITY" class="id_link">#</a></h3></div></div></div><p>657 As of PostgreSQL 9.0, <code class="type">hstore</code> uses a different internal658 representation than previous versions. This presents no obstacle for659 dump/restore upgrades since the text representation (used in the dump) is660 unchanged.661 </p><p>662 In the event of a binary upgrade, upward compatibility is maintained by663 having the new code recognize old-format data. This will entail a slight664 performance penalty when processing data that has not yet been modified by665 the new code. It is possible to force an upgrade of all values in a table666 column by doing an <code class="literal">UPDATE</code> statement as follows:667</p><pre class="programlisting">668UPDATE tablename SET hstorecol = hstorecol || '';669</pre><p>670 </p><p>671 Another way to do it is:672</p><pre class="programlisting">673ALTER TABLE tablename ALTER hstorecol TYPE hstore USING hstorecol || '';674</pre><p>675 The <code class="command">ALTER TABLE</code> method requires an676 <code class="literal">ACCESS EXCLUSIVE</code> lock on the table,677 but does not result in bloating the table with old row versions.678 </p></div><div class="sect2" id="HSTORE-TRANSFORMS"><div class="titlepage"><div><div><h3 class="title">F.18.7. Transforms <a href="#HSTORE-TRANSFORMS" class="id_link">#</a></h3></div></div></div><p>679 Additional extensions are available that implement transforms for680 the <code class="type">hstore</code> type for the languages PL/Perl and PL/Python. The681 extensions for PL/Perl are called <code class="literal">hstore_plperl</code>682 and <code class="literal">hstore_plperlu</code>, for trusted and untrusted PL/Perl.683 If you install these transforms and specify them when creating a684 function, <code class="type">hstore</code> values are mapped to Perl hashes. The685 extension for PL/Python is called <code class="literal">hstore_plpython3u</code>.686 If you use it, <code class="type">hstore</code> values are mapped to Python dictionaries.687 </p><div class="caution"><h3 class="title">Caution</h3><p>688 It is strongly recommended that the transform extensions be installed in689 the same schema as <code class="filename">hstore</code>. Otherwise there are690 installation-time security hazards if a transform extension's schema691 contains objects defined by a hostile user.692 </p></div></div><div class="sect2" id="HSTORE-AUTHORS"><div class="titlepage"><div><div><h3 class="title">F.18.8. Authors <a href="#HSTORE-AUTHORS" class="id_link">#</a></h3></div></div></div><p>693 Oleg Bartunov <code class="email"><<a class="email" href="mailto:oleg@sai.msu.su">oleg@sai.msu.su</a>></code>, Moscow, Moscow University, Russia694 </p><p>695 Teodor Sigaev <code class="email"><<a class="email" href="mailto:teodor@sigaev.ru">teodor@sigaev.ru</a>></code>, Moscow, Delta-Soft Ltd., Russia696 </p><p>697 Additional enhancements by Andrew Gierth <code class="email"><<a class="email" href="mailto:andrew@tao11.riddles.org.uk">andrew@tao11.riddles.org.uk</a>></code>,698 United Kingdom699 </p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="fuzzystrmatch.html" title="F.17. fuzzystrmatch — determine string similarities and distance">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="contrib.html" title="Appendix F. Additional Supplied Modules and Extensions">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="intagg.html" title="F.19. intagg — integer aggregator and enumerator">Next</a></td></tr><tr><td width="40%" align="left" valign="top">F.17. fuzzystrmatch — determine string similarities and distance </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"> F.19. intagg — integer aggregator and enumerator</td></tr></table></div></body></html>