1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
|
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!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</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" /><link rel="next" href="isn.html" title="F.21. isn" /></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</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="intagg.html" title="F.19. intagg">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="contrib.html" title="Appendix F. Additional Supplied Modules">Up</a></td><th width="60%" align="center">Appendix F. Additional Supplied Modules</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 15.5 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="isn.html" title="F.21. isn">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</h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="intarray.html#id-1.11.7.29.7">F.20.1. <code class="filename">intarray</code> Functions and Operators</a></span></dt><dt><span class="sect2"><a href="intarray.html#id-1.11.7.29.8">F.20.2. Index Support</a></span></dt><dt><span class="sect2"><a href="intarray.html#id-1.11.7.29.9">F.20.3. Example</a></span></dt><dt><span class="sect2"><a href="intarray.html#id-1.11.7.29.10">F.20.4. Benchmark</a></span></dt><dt><span class="sect2"><a href="intarray.html#id-1.11.7.29.11">F.20.5. Authors</a></span></dt></dl></div><a id="id-1.11.7.29.2" class="indexterm"></a><p>
The <code class="filename">intarray</code> module provides a number of useful functions
and operators for manipulating null-free arrays of integers.
There is also support for indexed searches using some of the operators.
</p><p>
All of these operations will throw an error if a supplied array contains any
NULL elements.
</p><p>
Many of these operations are only sensible for one-dimensional arrays.
Although they will accept input arrays of more dimensions, the data is
treated as though it were a linear array in storage order.
</p><p>
This module is considered <span class="quote">“<span class="quote">trusted</span>”</span>, that is, it can be
installed by non-superusers who have <code class="literal">CREATE</code> privilege
on the current database.
</p><div class="sect2" id="id-1.11.7.29.7"><div class="titlepage"><div><div><h3 class="title">F.20.1. <code class="filename">intarray</code> Functions and Operators</h3></div></div></div><p>
The functions provided by the <code class="filename">intarray</code> module
are shown in <a class="xref" href="intarray.html#INTARRAY-FUNC-TABLE" title="Table F.9. intarray Functions">Table F.9</a>, the operators
in <a class="xref" href="intarray.html#INTARRAY-OP-TABLE" title="Table F.10. intarray Operators">Table F.10</a>.
</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">
Function
</p>
<p>
Description
</p>
<p>
Example(s)
</p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.11.7.29.7.3.2.2.1.1.1.1" class="indexterm"></a>
<code class="function">icount</code> ( <code class="type">integer[]</code> )
→ <code class="returnvalue">integer</code>
</p>
<p>
Returns the number of elements in the array.
</p>
<p>
<code class="literal">icount('{1,2,3}'::integer[])</code>
→ <code class="returnvalue">3</code>
</p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.11.7.29.7.3.2.2.2.1.1.1" class="indexterm"></a>
<code class="function">sort</code> ( <code class="type">integer[]</code>, <em class="parameter"><code>dir</code></em> <code class="type">text</code> )
→ <code class="returnvalue">integer[]</code>
</p>
<p>
Sorts the array in either ascending or descending order.
<em class="parameter"><code>dir</code></em> must be <code class="literal">asc</code>
or <code class="literal">desc</code>.
</p>
<p>
<code class="literal">sort('{1,3,2}'::integer[], 'desc')</code>
→ <code class="returnvalue">{3,2,1}</code>
</p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
<code class="function">sort</code> ( <code class="type">integer[]</code> )
→ <code class="returnvalue">integer[]</code>
</p>
<p class="func_signature">
<a id="id-1.11.7.29.7.3.2.2.3.1.2.1" class="indexterm"></a>
<code class="function">sort_asc</code> ( <code class="type">integer[]</code> )
→ <code class="returnvalue">integer[]</code>
</p>
<p>
Sorts in ascending order.
</p>
<p>
<code class="literal">sort(array[11,77,44])</code>
→ <code class="returnvalue">{11,44,77}</code>
</p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.11.7.29.7.3.2.2.4.1.1.1" class="indexterm"></a>
<code class="function">sort_desc</code> ( <code class="type">integer[]</code> )
→ <code class="returnvalue">integer[]</code>
</p>
<p>
Sorts in descending order.
</p>
<p>
<code class="literal">sort_desc(array[11,77,44])</code>
→ <code class="returnvalue">{77,44,11}</code>
</p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.11.7.29.7.3.2.2.5.1.1.1" class="indexterm"></a>
<code class="function">uniq</code> ( <code class="type">integer[]</code> )
→ <code class="returnvalue">integer[]</code>
</p>
<p>
Removes adjacent duplicates.
Often used with <code class="function">sort</code> to remove all duplicates.
</p>
<p>
<code class="literal">uniq('{1,2,2,3,1,1}'::integer[])</code>
→ <code class="returnvalue">{1,2,3,1}</code>
</p>
<p>
<code class="literal">uniq(sort('{1,2,3,2,1}'::integer[]))</code>
→ <code class="returnvalue">{1,2,3}</code>
</p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.11.7.29.7.3.2.2.6.1.1.1" class="indexterm"></a>
<code class="function">idx</code> ( <code class="type">integer[]</code>, <em class="parameter"><code>item</code></em> <code class="type">integer</code> )
→ <code class="returnvalue">integer</code>
</p>
<p>
Returns index of the first array element
matching <em class="parameter"><code>item</code></em>, or 0 if no match.
</p>
<p>
<code class="literal">idx(array[11,22,33,22,11], 22)</code>
→ <code class="returnvalue">2</code>
</p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.11.7.29.7.3.2.2.7.1.1.1" class="indexterm"></a>
<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> )
→ <code class="returnvalue">integer[]</code>
</p>
<p>
Extracts the portion of the array starting at
position <em class="parameter"><code>start</code></em>, with <em class="parameter"><code>len</code></em>
elements.
</p>
<p>
<code class="literal">subarray('{1,2,3,2,1}'::integer[], 2, 3)</code>
→ <code class="returnvalue">{2,3,2}</code>
</p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
<code class="function">subarray</code> ( <code class="type">integer[]</code>, <em class="parameter"><code>start</code></em> <code class="type">integer</code> )
→ <code class="returnvalue">integer[]</code>
</p>
<p>
Extracts the portion of the array starting at
position <em class="parameter"><code>start</code></em>.
</p>
<p>
<code class="literal">subarray('{1,2,3,2,1}'::integer[], 2)</code>
→ <code class="returnvalue">{2,3,2,1}</code>
</p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.11.7.29.7.3.2.2.9.1.1.1" class="indexterm"></a>
<code class="function">intset</code> ( <code class="type">integer</code> )
→ <code class="returnvalue">integer[]</code>
</p>
<p>
Makes a single-element array.
</p>
<p>
<code class="literal">intset(42)</code>
→ <code class="returnvalue">{42}</code>
</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">
Operator
</p>
<p>
Description
</p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">
<code class="type">integer[]</code> <code class="literal">&&</code> <code class="type">integer[]</code>
→ <code class="returnvalue">boolean</code>
</p>
<p>
Do arrays overlap (have at least one element in common)?
</p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
<code class="type">integer[]</code> <code class="literal">@></code> <code class="type">integer[]</code>
→ <code class="returnvalue">boolean</code>
</p>
<p>
Does left array contain right array?
</p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
<code class="type">integer[]</code> <code class="literal"><@</code> <code class="type">integer[]</code>
→ <code class="returnvalue">boolean</code>
</p>
<p>
Is left array contained in right array?
</p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
<code class="type"></code> <code class="literal">#</code> <code class="type">integer[]</code>
→ <code class="returnvalue">integer</code>
</p>
<p>
Returns the number of elements in the array.
</p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
<code class="type">integer[]</code> <code class="literal">#</code> <code class="type">integer</code>
→ <code class="returnvalue">integer</code>
</p>
<p>
Returns index of the first array element
matching the right argument, or 0 if no match.
(Same as <code class="function">idx</code> function.)
</p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
<code class="type">integer[]</code> <code class="literal">+</code> <code class="type">integer</code>
→ <code class="returnvalue">integer[]</code>
</p>
<p>
Adds element to end of array.
</p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
<code class="type">integer[]</code> <code class="literal">+</code> <code class="type">integer[]</code>
→ <code class="returnvalue">integer[]</code>
</p>
<p>
Concatenates the arrays.
</p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
<code class="type">integer[]</code> <code class="literal">-</code> <code class="type">integer</code>
→ <code class="returnvalue">integer[]</code>
</p>
<p>
Removes entries matching the right argument from the array.
</p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
<code class="type">integer[]</code> <code class="literal">-</code> <code class="type">integer[]</code>
→ <code class="returnvalue">integer[]</code>
</p>
<p>
Removes elements of the right array from the left array.
</p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
<code class="type">integer[]</code> <code class="literal">|</code> <code class="type">integer</code>
→ <code class="returnvalue">integer[]</code>
</p>
<p>
Computes the union of the arguments.
</p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
<code class="type">integer[]</code> <code class="literal">|</code> <code class="type">integer[]</code>
→ <code class="returnvalue">integer[]</code>
</p>
<p>
Computes the union of the arguments.
</p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
<code class="type">integer[]</code> <code class="literal">&</code> <code class="type">integer[]</code>
→ <code class="returnvalue">integer[]</code>
</p>
<p>
Computes the intersection of the arguments.
</p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
<code class="type">integer[]</code> <code class="literal">@@</code> <code class="type">query_int</code>
→ <code class="returnvalue">boolean</code>
</p>
<p>
Does array satisfy query? (see below)
</p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
<code class="type">query_int</code> <code class="literal">~~</code> <code class="type">integer[]</code>
→ <code class="returnvalue">boolean</code>
</p>
<p>
Does array satisfy query? (commutator of <code class="literal">@@</code>)
</p></td></tr></tbody></table></div></div><br class="table-break" /><p>
The operators <code class="literal">&&</code>, <code class="literal">@></code> and
<code class="literal"><@</code> are equivalent to <span class="productname">PostgreSQL</span>'s built-in
operators of the same names, except that they work only on integer arrays
that do not contain nulls, while the built-in operators work for any array
type. This restriction makes them faster than the built-in operators
in many cases.
</p><p>
The <code class="literal">@@</code> and <code class="literal">~~</code> operators test whether an array
satisfies a <em class="firstterm">query</em>, which is expressed as a value of a
specialized data type <code class="type">query_int</code>. A <em class="firstterm">query</em>
consists of integer values that are checked against the elements of
the array, possibly combined using the operators <code class="literal">&</code>
(AND), <code class="literal">|</code> (OR), and <code class="literal">!</code> (NOT). Parentheses
can be used as needed. For example,
the query <code class="literal">1&(2|3)</code> matches arrays that contain 1
and also contain either 2 or 3.
</p></div><div class="sect2" id="id-1.11.7.29.8"><div class="titlepage"><div><div><h3 class="title">F.20.2. Index Support</h3></div></div></div><p>
<code class="filename">intarray</code> provides index support for the
<code class="literal">&&</code>, <code class="literal">@></code>,
and <code class="literal">@@</code> operators, as well as regular array equality.
</p><p>
Two parameterized GiST index operator classes are provided:
<code class="literal">gist__int_ops</code> (used by default) is suitable for
small- to medium-size data sets, while
<code class="literal">gist__intbig_ops</code> uses a larger signature and is more
suitable for indexing large data sets (i.e., columns containing
a large number of distinct array values).
The implementation uses an RD-tree data structure with
built-in lossy compression.
</p><p>
<code class="literal">gist__int_ops</code> approximates an integer set as an array of
integer ranges. Its optional integer parameter <code class="literal">numranges</code>
determines the maximum number of ranges in
one index key. The default value of <code class="literal">numranges</code> is 100.
Valid values are between 1 and 253. Using larger arrays as GiST index
keys leads to a more precise search (scanning a smaller fraction of the index and
fewer heap pages), at the cost of a larger index.
</p><p>
<code class="literal">gist__intbig_ops</code> approximates an integer set as a bitmap
signature. Its optional integer parameter <code class="literal">siglen</code>
determines the signature length in bytes.
The default signature length is 16 bytes. Valid values of signature length
are between 1 and 2024 bytes. Longer signatures lead to a more precise
search (scanning a smaller fraction of the index and fewer heap pages), at
the cost of a larger index.
</p><p>
There is also a non-default GIN operator class
<code class="literal">gin__int_ops</code>, which supports these operators as well
as <code class="literal"><@</code>.
</p><p>
The choice between GiST and GIN indexing depends on the relative
performance characteristics of GiST and GIN, which are discussed elsewhere.
</p></div><div class="sect2" id="id-1.11.7.29.9"><div class="titlepage"><div><div><h3 class="title">F.20.3. Example</h3></div></div></div><pre class="programlisting">
-- a message can be in one or more <span class="quote">“<span class="quote">sections</span>”</span>
CREATE TABLE message (mid INT PRIMARY KEY, sections INT[], ...);
-- create specialized index with signature length of 32 bytes
CREATE INDEX message_rdtree_idx ON message USING GIST (sections gist__intbig_ops (siglen = 32));
-- select messages in section 1 OR 2 - OVERLAP operator
SELECT message.mid FROM message WHERE message.sections && '{1,2}';
-- select messages in sections 1 AND 2 - CONTAINS operator
SELECT message.mid FROM message WHERE message.sections @> '{1,2}';
-- the same, using QUERY operator
SELECT message.mid FROM message WHERE message.sections @@ '1&2'::query_int;
</pre></div><div class="sect2" id="id-1.11.7.29.10"><div class="titlepage"><div><div><h3 class="title">F.20.4. Benchmark</h3></div></div></div><p>
The source directory <code class="filename">contrib/intarray/bench</code> contains a
benchmark test suite, which can be run against an installed
<span class="productname">PostgreSQL</span> server. (It also requires <code class="filename">DBD::Pg</code>
to be installed.) To run:
</p><pre class="programlisting">
cd .../contrib/intarray/bench
createdb TEST
psql -c "CREATE EXTENSION intarray" TEST
./create_test.pl | psql TEST
./bench.pl
</pre><p>
The <code class="filename">bench.pl</code> script has numerous options, which
are displayed when it is run without any arguments.
</p></div><div class="sect2" id="id-1.11.7.29.11"><div class="titlepage"><div><div><h3 class="title">F.20.5. Authors</h3></div></div></div><p>
All work was done by Teodor Sigaev (<code class="email"><<a class="email" href="mailto:teodor@sigaev.ru">teodor@sigaev.ru</a>></code>) and
Oleg Bartunov (<code class="email"><<a class="email" href="mailto:oleg@sai.msu.su">oleg@sai.msu.su</a>></code>). See
<a class="ulink" href="http://www.sai.msu.su/~megera/postgres/gist/" target="_top">http://www.sai.msu.su/~megera/postgres/gist/</a> for
additional information. Andrey Oktyabrski did a great work on adding new
functions and operations.
</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">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="contrib.html" title="Appendix F. Additional Supplied Modules">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="isn.html" title="F.21. isn">Next</a></td></tr><tr><td width="40%" align="left" valign="top">F.19. intagg </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 15.5 Documentation">Home</a></td><td width="40%" align="right" valign="top"> F.21. isn</td></tr></table></div></body></html>
|
