Team Ai
Datasetpublic

codekingpro/portable-devtools

sourceHugging Faceupdated 5mo agoView on Hugging Face
1likes15kdownloads
intarray.html321 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.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">&amp;&amp;</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">@&gt;</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">&lt;@</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">&amp;</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">&amp;&amp;</code>, <code class="literal">@&gt;</code> and235   <code class="literal">&lt;@</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">&amp;</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&amp;(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">&amp;&amp;</code>, <code class="literal">@&gt;</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">&lt;@</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 &amp;&amp; '{1,2}';295 296-- select messages in sections 1 AND 2 - CONTAINS operator297SELECT message.mid FROM message WHERE message.sections @&gt; '{1,2}';298 299-- the same, using QUERY operator300SELECT message.mid FROM message WHERE message.sections @@ '1&amp;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">&lt;<a class="email" href="mailto:teodor@sigaev.ru">teodor@sigaev.ru</a>&gt;</code>) and317   Oleg Bartunov (<code class="email">&lt;<a class="email" href="mailto:oleg@sai.msu.su">oleg@sai.msu.su</a>&gt;</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>
codekingpro/portable-devtools · Team Ai