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.20. intarray — manipulate arrays of integers</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="intagg.html" title="F.19. intagg — integer aggregator and enumerator" /><link rel="next" href="isn.html" title="F.21. isn — data types for international standard numbers (ISBN, EAN, UPC, etc.)" /></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.20. intarray — manipulate arrays of integers</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="intagg.html" title="F.19. intagg — integer aggregator and enumerator">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="isn.html" title="F.21. isn — data types for international standard numbers (ISBN, EAN, UPC, etc.)">Next</a></td></tr></table><hr /></div><div class="sect1" id="INTARRAY"><div class="titlepage"><div><div><h2 class="title" style="clear: both">F.20. intarray — manipulate arrays of integers <a href="#INTARRAY" class="id_link">#</a></h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="intarray.html#INTARRAY-FUNCS-OPS">F.20.1. <code class="filename">intarray</code> Functions and Operators</a></span></dt><dt><span class="sect2"><a href="intarray.html#INTARRAY-INDEX">F.20.2. Index Support</a></span></dt><dt><span class="sect2"><a href="intarray.html#INTARRAY-EXAMPLE">F.20.3. Example</a></span></dt><dt><span class="sect2"><a href="intarray.html#INTARRAY-BENCHMARK">F.20.4. Benchmark</a></span></dt><dt><span class="sect2"><a href="intarray.html#INTARRAY-AUTHORS">F.20.5. Authors</a></span></dt></dl></div><a id="id-1.11.7.30.2" class="indexterm"></a><p>3 The <code class="filename">intarray</code> module provides a number of useful functions4 and operators for manipulating null-free arrays of integers.5 There is also support for indexed searches using some of the operators.6 </p><p>7 All of these operations will throw an error if a supplied array contains any8 NULL elements.9 </p><p>10 Many of these operations are only sensible for one-dimensional arrays.11 Although they will accept input arrays of more dimensions, the data is12 treated as though it were a linear array in storage order.13 </p><p>14 This module is considered <span class="quote">“<span class="quote">trusted</span>”</span>, that is, it can be15 installed by non-superusers who have <code class="literal">CREATE</code> privilege16 on the current database.17 </p><div class="sect2" id="INTARRAY-FUNCS-OPS"><div class="titlepage"><div><div><h3 class="title">F.20.1. <code class="filename">intarray</code> Functions and Operators <a href="#INTARRAY-FUNCS-OPS" class="id_link">#</a></h3></div></div></div><p>18 The functions provided by the <code class="filename">intarray</code> module19 are shown in <a class="xref" href="intarray.html#INTARRAY-FUNC-TABLE" title="Table F.9. intarray Functions">Table F.9</a>, the operators20 in <a class="xref" href="intarray.html#INTARRAY-OP-TABLE" title="Table F.10. intarray Operators">Table F.10</a>.21 </p><div class="table" id="INTARRAY-FUNC-TABLE"><p class="title"><strong>Table F.9. <code class="filename">intarray</code> Functions</strong></p><div class="table-contents"><table class="table" summary="intarray Functions" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">22 Function23 </p>24 <p>25 Description26 </p>27 <p>28 Example(s)29 </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">30 <a id="id-1.11.7.30.7.3.2.2.1.1.1.1" class="indexterm"></a>31 <code class="function">icount</code> ( <code class="type">integer[]</code> )32 → <code class="returnvalue">integer</code>33 </p>34 <p>35 Returns the number of elements in the array.36 </p>37 <p>38 <code class="literal">icount('{1,2,3}'::integer[])</code>39 → <code class="returnvalue">3</code>40 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">41 <a id="id-1.11.7.30.7.3.2.2.2.1.1.1" class="indexterm"></a>42 <code class="function">sort</code> ( <code class="type">integer[]</code>, <em class="parameter"><code>dir</code></em> <code class="type">text</code> )43 → <code class="returnvalue">integer[]</code>44 </p>45 <p>46 Sorts the array in either ascending or descending order.47 <em class="parameter"><code>dir</code></em> must be <code class="literal">asc</code>48 or <code class="literal">desc</code>.49 </p>50 <p>51 <code class="literal">sort('{1,3,2}'::integer[], 'desc')</code>52 → <code class="returnvalue">{3,2,1}</code>53 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">54 <code class="function">sort</code> ( <code class="type">integer[]</code> )55 → <code class="returnvalue">integer[]</code>56 </p>57 <p class="func_signature">58 <a id="id-1.11.7.30.7.3.2.2.3.1.2.1" class="indexterm"></a>59 <code class="function">sort_asc</code> ( <code class="type">integer[]</code> )60 → <code class="returnvalue">integer[]</code>61 </p>62 <p>63 Sorts in ascending order.64 </p>65 <p>66 <code class="literal">sort(array[11,77,44])</code>67 → <code class="returnvalue">{11,44,77}</code>68 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">69 <a id="id-1.11.7.30.7.3.2.2.4.1.1.1" class="indexterm"></a>70 <code class="function">sort_desc</code> ( <code class="type">integer[]</code> )71 → <code class="returnvalue">integer[]</code>72 </p>73 <p>74 Sorts in descending order.75 </p>76 <p>77 <code class="literal">sort_desc(array[11,77,44])</code>78 → <code class="returnvalue">{77,44,11}</code>79 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">80 <a id="id-1.11.7.30.7.3.2.2.5.1.1.1" class="indexterm"></a>81 <code class="function">uniq</code> ( <code class="type">integer[]</code> )82 → <code class="returnvalue">integer[]</code>83 </p>84 <p>85 Removes adjacent duplicates.86 Often used with <code class="function">sort</code> to remove all duplicates.87 </p>88 <p>89 <code class="literal">uniq('{1,2,2,3,1,1}'::integer[])</code>90 → <code class="returnvalue">{1,2,3,1}</code>91 </p>92 <p>93 <code class="literal">uniq(sort('{1,2,3,2,1}'::integer[]))</code>94 → <code class="returnvalue">{1,2,3}</code>95 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">96 <a id="id-1.11.7.30.7.3.2.2.6.1.1.1" class="indexterm"></a>97 <code class="function">idx</code> ( <code class="type">integer[]</code>, <em class="parameter"><code>item</code></em> <code class="type">integer</code> )98 → <code class="returnvalue">integer</code>99 </p>100 <p>101 Returns index of the first array element102 matching <em class="parameter"><code>item</code></em>, or 0 if no match.103 </p>104 <p>105 <code class="literal">idx(array[11,22,33,22,11], 22)</code>106 → <code class="returnvalue">2</code>107 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">108 <a id="id-1.11.7.30.7.3.2.2.7.1.1.1" class="indexterm"></a>109 <code class="function">subarray</code> ( <code class="type">integer[]</code>, <em class="parameter"><code>start</code></em> <code class="type">integer</code>, <em class="parameter"><code>len</code></em> <code class="type">integer</code> )110 → <code class="returnvalue">integer[]</code>111 </p>112 <p>113 Extracts the portion of the array starting at114 position <em class="parameter"><code>start</code></em>, with <em class="parameter"><code>len</code></em>115 elements.116 </p>117 <p>118 <code class="literal">subarray('{1,2,3,2,1}'::integer[], 2, 3)</code>119 → <code class="returnvalue">{2,3,2}</code>120 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">121 <code class="function">subarray</code> ( <code class="type">integer[]</code>, <em class="parameter"><code>start</code></em> <code class="type">integer</code> )122 → <code class="returnvalue">integer[]</code>123 </p>124 <p>125 Extracts the portion of the array starting at126 position <em class="parameter"><code>start</code></em>.127 </p>128 <p>129 <code class="literal">subarray('{1,2,3,2,1}'::integer[], 2)</code>130 → <code class="returnvalue">{2,3,2,1}</code>131 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">132 <a id="id-1.11.7.30.7.3.2.2.9.1.1.1" class="indexterm"></a>133 <code class="function">intset</code> ( <code class="type">integer</code> )134 → <code class="returnvalue">integer[]</code>135 </p>136 <p>137 Makes a single-element array.138 </p>139 <p>140 <code class="literal">intset(42)</code>141 → <code class="returnvalue">{42}</code>142 </p></td></tr></tbody></table></div></div><br class="table-break" /><div class="table" id="INTARRAY-OP-TABLE"><p class="title"><strong>Table F.10. <code class="filename">intarray</code> Operators</strong></p><div class="table-contents"><table class="table" summary="intarray Operators" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">143 Operator144 </p>145 <p>146 Description147 </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">148 <code class="type">integer[]</code> <code class="literal">&&</code> <code class="type">integer[]</code>149 → <code class="returnvalue">boolean</code>150 </p>151 <p>152 Do arrays overlap (have at least one element in common)?153 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">154 <code class="type">integer[]</code> <code class="literal">@></code> <code class="type">integer[]</code>155 → <code class="returnvalue">boolean</code>156 </p>157 <p>158 Does left array contain right array?159 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">160 <code class="type">integer[]</code> <code class="literal"><@</code> <code class="type">integer[]</code>161 → <code class="returnvalue">boolean</code>162 </p>163 <p>164 Is left array contained in right array?165 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">166 <code class="type"></code> <code class="literal">#</code> <code class="type">integer[]</code>167 → <code class="returnvalue">integer</code>168 </p>169 <p>170 Returns the number of elements in the array.171 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">172 <code class="type">integer[]</code> <code class="literal">#</code> <code class="type">integer</code>173 → <code class="returnvalue">integer</code>174 </p>175 <p>176 Returns index of the first array element177 matching the right argument, or 0 if no match.178 (Same as <code class="function">idx</code> function.)179 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">180 <code class="type">integer[]</code> <code class="literal">+</code> <code class="type">integer</code>181 → <code class="returnvalue">integer[]</code>182 </p>183 <p>184 Adds element to end of array.185 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">186 <code class="type">integer[]</code> <code class="literal">+</code> <code class="type">integer[]</code>187 → <code class="returnvalue">integer[]</code>188 </p>189 <p>190 Concatenates the arrays.191 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">192 <code class="type">integer[]</code> <code class="literal">-</code> <code class="type">integer</code>193 → <code class="returnvalue">integer[]</code>194 </p>195 <p>196 Removes entries matching the right argument from the array.197 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">198 <code class="type">integer[]</code> <code class="literal">-</code> <code class="type">integer[]</code>199 → <code class="returnvalue">integer[]</code>200 </p>201 <p>202 Removes elements of the right array from the left array.203 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">204 <code class="type">integer[]</code> <code class="literal">|</code> <code class="type">integer</code>205 → <code class="returnvalue">integer[]</code>206 </p>207 <p>208 Computes the union of the arguments.209 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">210 <code class="type">integer[]</code> <code class="literal">|</code> <code class="type">integer[]</code>211 → <code class="returnvalue">integer[]</code>212 </p>213 <p>214 Computes the union of the arguments.215 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">216 <code class="type">integer[]</code> <code class="literal">&</code> <code class="type">integer[]</code>217 → <code class="returnvalue">integer[]</code>218 </p>219 <p>220 Computes the intersection of the arguments.221 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">222 <code class="type">integer[]</code> <code class="literal">@@</code> <code class="type">query_int</code>223 → <code class="returnvalue">boolean</code>224 </p>225 <p>226 Does array satisfy query? (see below)227 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">228 <code class="type">query_int</code> <code class="literal">~~</code> <code class="type">integer[]</code>229 → <code class="returnvalue">boolean</code>230 </p>231 <p>232 Does array satisfy query? (commutator of <code class="literal">@@</code>)233 </p></td></tr></tbody></table></div></div><br class="table-break" /><p>234 The operators <code class="literal">&&</code>, <code class="literal">@></code> and235 <code class="literal"><@</code> are equivalent to <span class="productname">PostgreSQL</span>'s built-in236 operators of the same names, except that they work only on integer arrays237 that do not contain nulls, while the built-in operators work for any array238 type. This restriction makes them faster than the built-in operators239 in many cases.240 </p><p>241 The <code class="literal">@@</code> and <code class="literal">~~</code> operators test whether an array242 satisfies a <em class="firstterm">query</em>, which is expressed as a value of a243 specialized data type <code class="type">query_int</code>. A <em class="firstterm">query</em>244 consists of integer values that are checked against the elements of245 the array, possibly combined using the operators <code class="literal">&</code>246 (AND), <code class="literal">|</code> (OR), and <code class="literal">!</code> (NOT). Parentheses247 can be used as needed. For example,248 the query <code class="literal">1&(2|3)</code> matches arrays that contain 1249 and also contain either 2 or 3.250 </p></div><div class="sect2" id="INTARRAY-INDEX"><div class="titlepage"><div><div><h3 class="title">F.20.2. Index Support <a href="#INTARRAY-INDEX" class="id_link">#</a></h3></div></div></div><p>251 <code class="filename">intarray</code> provides index support for the252 <code class="literal">&&</code>, <code class="literal">@></code>,253 and <code class="literal">@@</code> operators, as well as regular array equality.254 </p><p>255 Two parameterized GiST index operator classes are provided:256 <code class="literal">gist__int_ops</code> (used by default) is suitable for257 small- to medium-size data sets, while258 <code class="literal">gist__intbig_ops</code> uses a larger signature and is more259 suitable for indexing large data sets (i.e., columns containing260 a large number of distinct array values).261 The implementation uses an RD-tree data structure with262 built-in lossy compression.263 </p><p>264 <code class="literal">gist__int_ops</code> approximates an integer set as an array of265 integer ranges. Its optional integer parameter <code class="literal">numranges</code>266 determines the maximum number of ranges in267 one index key. The default value of <code class="literal">numranges</code> is 100.268 Valid values are between 1 and 253. Using larger arrays as GiST index269 keys leads to a more precise search (scanning a smaller fraction of the index and270 fewer heap pages), at the cost of a larger index.271 </p><p>272 <code class="literal">gist__intbig_ops</code> approximates an integer set as a bitmap273 signature. Its optional integer parameter <code class="literal">siglen</code>274 determines the signature length in bytes.275 The default signature length is 16 bytes. Valid values of signature length276 are between 1 and 2024 bytes. Longer signatures lead to a more precise277 search (scanning a smaller fraction of the index and fewer heap pages), at278 the cost of a larger index.279 </p><p>280 There is also a non-default GIN operator class281 <code class="literal">gin__int_ops</code>, which supports these operators as well282 as <code class="literal"><@</code>.283 </p><p>284 The choice between GiST and GIN indexing depends on the relative285 performance characteristics of GiST and GIN, which are discussed elsewhere.286 </p></div><div class="sect2" id="INTARRAY-EXAMPLE"><div class="titlepage"><div><div><h3 class="title">F.20.3. Example <a href="#INTARRAY-EXAMPLE" class="id_link">#</a></h3></div></div></div><pre class="programlisting">287-- a message can be in one or more <span class="quote">“<span class="quote">sections</span>”</span>288CREATE TABLE message (mid INT PRIMARY KEY, sections INT[], ...);289 290-- create specialized index with signature length of 32 bytes291CREATE INDEX message_rdtree_idx ON message USING GIST (sections gist__intbig_ops (siglen = 32));292 293-- select messages in section 1 OR 2 - OVERLAP operator294SELECT message.mid FROM message WHERE message.sections && '{1,2}';295 296-- select messages in sections 1 AND 2 - CONTAINS operator297SELECT message.mid FROM message WHERE message.sections @> '{1,2}';298 299-- the same, using QUERY operator300SELECT message.mid FROM message WHERE message.sections @@ '1&2'::query_int;301</pre></div><div class="sect2" id="INTARRAY-BENCHMARK"><div class="titlepage"><div><div><h3 class="title">F.20.4. Benchmark <a href="#INTARRAY-BENCHMARK" class="id_link">#</a></h3></div></div></div><p>302 The source directory <code class="filename">contrib/intarray/bench</code> contains a303 benchmark test suite, which can be run against an installed304 <span class="productname">PostgreSQL</span> server. (It also requires <code class="filename">DBD::Pg</code>305 to be installed.) To run:306 </p><pre class="programlisting">307cd .../contrib/intarray/bench308createdb TEST309psql -c "CREATE EXTENSION intarray" TEST310./create_test.pl | psql TEST311./bench.pl312</pre><p>313 The <code class="filename">bench.pl</code> script has numerous options, which314 are displayed when it is run without any arguments.315 </p></div><div class="sect2" id="INTARRAY-AUTHORS"><div class="titlepage"><div><div><h3 class="title">F.20.5. Authors <a href="#INTARRAY-AUTHORS" class="id_link">#</a></h3></div></div></div><p>316 All work was done by Teodor Sigaev (<code class="email"><<a class="email" href="mailto:teodor@sigaev.ru">teodor@sigaev.ru</a>></code>) and317 Oleg Bartunov (<code class="email"><<a class="email" href="mailto:oleg@sai.msu.su">oleg@sai.msu.su</a>></code>). See318 <a class="ulink" href="http://www.sai.msu.su/~megera/postgres/gist/" target="_top">http://www.sai.msu.su/~megera/postgres/gist/</a> for319 additional information. Andrey Oktyabrski did a great work on adding new320 functions and operations.321 </p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="intagg.html" title="F.19. intagg — integer aggregator and enumerator">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="isn.html" title="F.21. isn — data types for international standard numbers (ISBN, EAN, UPC, etc.)">Next</a></td></tr><tr><td width="40%" align="left" valign="top">F.19. intagg — integer aggregator and enumerator </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.21. isn — data types for international standard numbers (ISBN, EAN, UPC, etc.)</td></tr></table></div></body></html>