Team Ai
Datasetpublic

codekingpro/portable-devtools

sourceHugging Faceupdated 5mo agoView on Hugging Face
1likes15kdownloads
hstore.html699 linesDownload Raw Back to html
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">=&gt;</code>16   <em class="replaceable"><code>value</code></em> pairs separated by commas. Some examples:17 18</p><pre class="synopsis">19k =&gt; v20foo =&gt; bar, baz =&gt; whatever21"1-a" =&gt; "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">=&gt;</code> sign is26   ignored. Double-quote keys and values that include whitespace, commas,27   <code class="literal">=</code>s or <code class="literal">&gt;</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=&gt;1,a=&gt;2'::hstore;36  hstore37----------38 "a"=&gt;"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 =&gt; 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">-&gt;</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=&gt;x, b=&gt;y'::hstore -&gt; '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">-&gt;</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=&gt;x, b=&gt;y, c=&gt;z'::hstore -&gt; 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=&gt;b, c=&gt;d'::hstore || 'c=&gt;x, d=&gt;q'::hstore</code>105        → <code class="returnvalue">"a"=&gt;"b", "c"=&gt;"x", "d"=&gt;"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=&gt;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">?&amp;</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=&gt;1,b=&gt;2'::hstore ?&amp; 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=&gt;1,b=&gt;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">@&gt;</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=&gt;b, b=&gt;1, c=&gt;NULL'::hstore @&gt; 'b=&gt;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">&lt;@</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=&gt;c'::hstore &lt;@ 'a=&gt;b, b=&gt;1, c=&gt;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=&gt;1, b=&gt;2, c=&gt;3'::hstore - 'b'::text</code>165        → <code class="returnvalue">"a"=&gt;"1", "c"=&gt;"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=&gt;1, b=&gt;2, c=&gt;3'::hstore - ARRAY['a','b']</code>175        → <code class="returnvalue">"c"=&gt;"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=&gt;1, b=&gt;2, c=&gt;3'::hstore - 'a=&gt;4, b=&gt;2'::hstore</code>185        → <code class="returnvalue">"a"=&gt;"1", "c"=&gt;"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=&gt;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=&gt;foo, b=&gt;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=&gt;foo, b=&gt;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"=&gt;"1", "f2"=&gt;"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"=&gt;"1", "b"=&gt;"2"</code>248       </p>249       <p>250        <code class="literal">hstore(ARRAY[['c','3'],['d','4']])</code>251        → <code class="returnvalue">"c"=&gt;"3", "d"=&gt;"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"=&gt;"1", "b"=&gt;"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"=&gt;"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=&gt;1,b=&gt;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=&gt;1,b=&gt;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=&gt;1,b=&gt;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=&gt;1,b=&gt;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=&gt;1,b=&gt;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=&gt;1,b=&gt;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"=&gt;1, b=&gt;t, c=&gt;null, d=&gt;12345, e=&gt;012345, f=&gt;1.234, g=&gt;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"=&gt;1, b=&gt;t, c=&gt;null, d=&gt;12345, e=&gt;012345, f=&gt;1.234, g=&gt;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"=&gt;1, b=&gt;t, c=&gt;null, d=&gt;12345, e=&gt;012345, f=&gt;1.234, g=&gt;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"=&gt;1, b=&gt;t, c=&gt;null, d=&gt;12345, e=&gt;012345, f=&gt;1.234, g=&gt;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=&gt;1,b=&gt;2,c=&gt;3'::hstore, ARRAY['b','c','x'])</code>417        → <code class="returnvalue">"b"=&gt;"2", "c"=&gt;"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=&gt;1,b=&gt;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=&gt;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=&gt;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=&gt;1,b=&gt;2', 'b')</code>470        → <code class="returnvalue">"a"=&gt;"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=&gt;1,b=&gt;2,c=&gt;3', ARRAY['a','b'])</code>480        → <code class="returnvalue">"c"=&gt;"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=&gt;1,b=&gt;2', 'a=&gt;4,b=&gt;2'::hstore)</code>490        → <code class="returnvalue">"a"=&gt;"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=&gt;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=&gt;b, c=&gt;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"=&gt;"b", "c"=&gt;"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">-&gt;</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">@&gt;</code>,536   <code class="literal">?</code>, <code class="literal">?&amp;</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"=&gt;"123", "col2"=&gt;"foo", "col3"=&gt;"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"=&gt;"456", "col2"=&gt;"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"=&gt;"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=&gt;bq, b=&gt;NULL, ""=&gt;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">&lt;<a class="email" href="mailto:oleg@sai.msu.su">oleg@sai.msu.su</a>&gt;</code>, Moscow, Moscow University, Russia694  </p><p>695   Teodor Sigaev <code class="email">&lt;<a class="email" href="mailto:teodor@sigaev.ru">teodor@sigaev.ru</a>&gt;</code>, Moscow, Delta-Soft Ltd., Russia696  </p><p>697   Additional enhancements by Andrew Gierth <code class="email">&lt;<a class="email" href="mailto:andrew@tao11.riddles.org.uk">andrew@tao11.riddles.org.uk</a>&gt;</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>
codekingpro/portable-devtools · Team Ai