3
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
4
<title>8.10.�Arrays</title>
5
<link rel="stylesheet" href="stylesheet.css" type="text/css">
6
<link rev="made" href="pgsql-docs@postgresql.org">
7
<meta name="generator" content="DocBook XSL Stylesheets V1.64.1">
8
<link rel="home" href="index.html" title="PostgreSQL 8.0.0beta5 Documentation">
9
<link rel="up" href="datatype.html" title="Chapter�8.�Data Types">
10
<link rel="previous" href="datatype-bit.html" title="8.9.�Bit String Types">
11
<link rel="next" href="rowtypes.html" title="8.11.�Composite Types">
13
<body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="sect1" lang="en">
14
<div class="titlepage">
15
<div><div><h2 class="title" style="clear: both">
16
<a name="arrays"></a>8.10.�Arrays</h2></div></div>
19
<a name="id2537522"></a><p> <span class="productname">PostgreSQL</span> allows columns of a table to be
20
defined as variable-length multidimensional arrays. Arrays of any
21
built-in or user-defined base type can be created. (Arrays of
22
composite types or domains are not yet supported, however.)
24
<div class="sect2" lang="en">
25
<div class="titlepage">
26
<div><div><h3 class="title">
27
<a name="id2537539"></a>8.10.1.�Declaration of Array Types</h3></div></div>
30
<p> To illustrate the use of array types, we create this table:
32
<pre class="programlisting">CREATE TABLE sal_emp (
34
pay_by_quarter integer[],
38
As shown, an array data type is named by appending square brackets
39
(<tt class="literal">[]</tt>) to the data type name of the array elements. The
40
above command will create a table named
41
<tt class="structname">sal_emp</tt> with a column of type
42
<tt class="type">text</tt> (<tt class="structfield">name</tt>), a
43
one-dimensional array of type <tt class="type">integer</tt>
44
(<tt class="structfield">pay_by_quarter</tt>), which represents the
45
employee's salary by quarter, and a two-dimensional array of
46
<tt class="type">text</tt> (<tt class="structfield">schedule</tt>), which
47
represents the employee's weekly schedule.
49
<p> The syntax for <tt class="command">CREATE TABLE</tt> allows the exact size of
50
arrays to be specified, for example:
53
<pre class="programlisting">CREATE TABLE tictactoe (
58
However, the current implementation does not enforce the array size
59
limits [mdash ] the behavior is the same as for arrays of unspecified
62
<p> Actually, the current implementation does not enforce the declared
63
number of dimensions either. Arrays of a particular element type are
64
all considered to be of the same type, regardless of size or number
65
of dimensions. So, declaring number of dimensions or sizes in
66
<tt class="command">CREATE TABLE</tt> is simply documentation, it does not
67
affect runtime behavior.
69
<p> An alternative, SQL99-standard syntax may be used for one-dimensional arrays.
70
<tt class="structfield">pay_by_quarter</tt> could have been defined as:
72
<pre class="programlisting"> pay_by_quarter integer ARRAY[4],</pre>
74
This syntax requires an integer constant to denote the array size.
75
As before, however, <span class="productname">PostgreSQL</span> does not enforce the
79
<div class="sect2" lang="en">
80
<div class="titlepage">
81
<div><div><h3 class="title">
82
<a name="id2537647"></a>8.10.2.�Array Value Input</h3></div></div>
85
<a name="id2537650"></a><p> To write an array value as a literal constant, enclose the element
86
values within curly braces and separate them by commas. (If you
87
know C, this is not unlike the C syntax for initializing
88
structures.) You may put double quotes around any element value,
89
and must do so if it contains commas or curly braces. (More
90
details appear below.) Thus, the general format of an array
91
constant is the following:
93
<pre class="synopsis">'{ <i class="replaceable"><tt>val1</tt></i> <i class="replaceable"><tt>delim</tt></i> <i class="replaceable"><tt>val2</tt></i> <i class="replaceable"><tt>delim</tt></i> ... }'</pre>
95
where <i class="replaceable"><tt>delim</tt></i> is the delimiter character
96
for the type, as recorded in its <tt class="literal">pg_type</tt> entry.
97
Among the standard data types provided in the
98
<span class="productname">PostgreSQL</span> distribution, type
99
<tt class="literal">box</tt> uses a semicolon (<tt class="literal">;</tt>) but all the others
100
use comma (<tt class="literal">,</tt>). Each <i class="replaceable"><tt>val</tt></i> is
101
either a constant of the array element type, or a subarray. An example
102
of an array constant is
104
<pre class="programlisting">'{{1,2,3},{4,5,6},{7,8,9}}'</pre>
106
This constant is a two-dimensional, 3-by-3 array consisting of
107
three subarrays of integers.
109
<p> (These kinds of array constants are actually only a special case of
110
the generic type constants discussed in <a href="sql-syntax.html#sql-syntax-constants-generic" title="4.1.2.5.�Constants of Other Types">Section�4.1.2.5, “Constants of Other Types”</a>. The constant is initially
111
treated as a string and passed to the array input conversion
112
routine. An explicit type specification might be necessary.)
114
<p> Now we can show some <tt class="command">INSERT</tt> statements.
117
<pre class="programlisting">INSERT INTO sal_emp
119
'{10000, 10000, 10000, 10000}',
120
'{{"meeting", "lunch"}, {"meeting"}}');
121
ERROR: multidimensional arrays must have array expressions with matching dimensions</pre>
124
Note that multidimensional arrays must have matching extents for each
125
dimension. A mismatch causes an error report.
128
<pre class="programlisting">INSERT INTO sal_emp
130
'{10000, 10000, 10000, 10000}',
131
'{{"meeting", "lunch"}, {"training", "presentation"}}');
135
'{20000, 25000, 25000, 25000}',
136
'{{"breakfast", "consulting"}, {"meeting", "lunch"}}');</pre>
139
<p> A limitation of the present array implementation is that individual
140
elements of an array cannot be SQL null values. The entire array
141
can be set to null, but you can't have an array with some elements
144
<p> The result of the previous two inserts looks like this:
146
<pre class="programlisting">SELECT * FROM sal_emp;
147
name | pay_by_quarter | schedule
148
-------+---------------------------+-------------------------------------------
149
Bill | {10000,10000,10000,10000} | {{meeting,lunch},{training,presentation}}
150
Carol | {20000,25000,25000,25000} | {{breakfast,consulting},{meeting,lunch}}
154
<p> The <tt class="literal">ARRAY</tt> constructor syntax may also be used:
156
<pre class="programlisting">INSERT INTO sal_emp
158
ARRAY[10000, 10000, 10000, 10000],
159
ARRAY[['meeting', 'lunch'], ['training', 'presentation']]);
163
ARRAY[20000, 25000, 25000, 25000],
164
ARRAY[['breakfast', 'consulting'], ['meeting', 'lunch']]);</pre>
166
Notice that the array elements are ordinary SQL constants or
167
expressions; for instance, string literals are single quoted, instead of
168
double quoted as they would be in an array literal. The <tt class="literal">ARRAY</tt>
169
constructor syntax is discussed in more detail in
170
<a href="sql-expressions.html#sql-syntax-array-constructors" title="4.2.10.�Array Constructors">Section�4.2.10, “Array Constructors”</a>.
173
<div class="sect2" lang="en">
174
<div class="titlepage">
175
<div><div><h3 class="title">
176
<a name="id2537839"></a>8.10.3.�Accessing Arrays</h3></div></div>
179
<p> Now, we can run some queries on the table.
180
First, we show how to access a single element of an array at a time.
181
This query retrieves the names of the employees whose pay changed in
185
<pre class="programlisting">SELECT name FROM sal_emp WHERE pay_by_quarter[1] <> pay_by_quarter[2];
193
The array subscript numbers are written within square brackets.
194
By default <span class="productname">PostgreSQL</span> uses the
195
one-based numbering convention for arrays, that is,
196
an array of <i class="replaceable"><tt>n</tt></i> elements starts with <tt class="literal">array[1]</tt> and
197
ends with <tt class="literal">array[<i class="replaceable"><tt>n</tt></i>]</tt>.
199
<p> This query retrieves the third quarter pay of all employees:
202
<pre class="programlisting">SELECT pay_by_quarter[3] FROM sal_emp;
211
<p> We can also access arbitrary rectangular slices of an array, or
212
subarrays. An array slice is denoted by writing
213
<tt class="literal"><i class="replaceable"><tt>lower-bound</tt></i>:<i class="replaceable"><tt>upper-bound</tt></i></tt>
214
for one or more array dimensions. For example, this query retrieves the first
215
item on Bill's schedule for the first two days of the week:
218
<pre class="programlisting">SELECT schedule[1:2][1:1] FROM sal_emp WHERE name = 'Bill';
221
------------------------
222
{{meeting},{training}}
226
We could also have written
229
<pre class="programlisting">SELECT schedule[1:2][1] FROM sal_emp WHERE name = 'Bill';</pre>
232
with the same result. An array subscripting operation is always taken to
233
represent an array slice if any of the subscripts are written in the form
234
<tt class="literal"><i class="replaceable"><tt>lower</tt></i>:<i class="replaceable"><tt>upper</tt></i></tt>.
235
A lower bound of 1 is assumed for any subscript where only one value
236
is specified, as in this example:
238
<pre class="programlisting">SELECT schedule[1:2][2] FROM sal_emp WHERE name = 'Bill';
241
-------------------------------------------
242
{{meeting,lunch},{training,presentation}}
246
<p> The current dimensions of any array value can be retrieved with the
247
<tt class="function">array_dims</tt> function:
250
<pre class="programlisting">SELECT array_dims(schedule) FROM sal_emp WHERE name = 'Carol';
258
<tt class="function">array_dims</tt> produces a <tt class="type">text</tt> result,
259
which is convenient for people to read but perhaps not so convenient
260
for programs. Dimensions can also be retrieved with
261
<tt class="function">array_upper</tt> and <tt class="function">array_lower</tt>,
262
which return the upper and lower bound of a
263
specified array dimension, respectively.
266
<pre class="programlisting">SELECT array_upper(schedule, 1) FROM sal_emp WHERE name = 'Carol';
275
<div class="sect2" lang="en">
276
<div class="titlepage">
277
<div><div><h3 class="title">
278
<a name="id2538000"></a>8.10.4.�Modifying Arrays</h3></div></div>
281
<p> An array value can be replaced completely:
284
<pre class="programlisting">UPDATE sal_emp SET pay_by_quarter = '{25000,25000,27000,27000}'
285
WHERE name = 'Carol';</pre>
288
or using the <tt class="literal">ARRAY</tt> expression syntax:
291
<pre class="programlisting">UPDATE sal_emp SET pay_by_quarter = ARRAY[25000,25000,27000,27000]
292
WHERE name = 'Carol';</pre>
295
An array may also be updated at a single element:
298
<pre class="programlisting">UPDATE sal_emp SET pay_by_quarter[4] = 15000
299
WHERE name = 'Bill';</pre>
302
or updated in a slice:
305
<pre class="programlisting">UPDATE sal_emp SET pay_by_quarter[1:2] = '{27000,27000}'
306
WHERE name = 'Carol';</pre>
310
<p> A stored array value can be enlarged by assigning to an element adjacent to
311
those already present, or by assigning to a slice that is adjacent
312
to or overlaps the data already present. For example, if array
313
<tt class="literal">myarray</tt> currently has 4 elements, it will have five
314
elements after an update that assigns to <tt class="literal">myarray[5]</tt>.
315
Currently, enlargement in this fashion is only allowed for one-dimensional
316
arrays, not multidimensional arrays.
318
<p> Array slice assignment allows creation of arrays that do not use one-based
319
subscripts. For example one might assign to <tt class="literal">myarray[-2:7]</tt> to
320
create an array with subscript values running from -2 to 7.
322
<p> New array values can also be constructed by using the concatenation operator,
323
<tt class="literal">||</tt>.
325
<pre class="programlisting">SELECT ARRAY[1,2] || ARRAY[3,4];
331
SELECT ARRAY[5,6] || ARRAY[[1,2],[3,4]];
333
---------------------
338
<p> The concatenation operator allows a single element to be pushed on to the
339
beginning or end of a one-dimensional array. It also accepts two
340
<i class="replaceable"><tt>N</tt></i>-dimensional arrays, or an <i class="replaceable"><tt>N</tt></i>-dimensional
341
and an <i class="replaceable"><tt>N+1</tt></i>-dimensional array.
343
<p> When a single element is pushed on to the beginning of a one-dimensional
344
array, the result is an array with a lower bound subscript equal to
345
the right-hand operand's lower bound subscript, minus one. When a single
346
element is pushed on to the end of a one-dimensional array, the result is
347
an array retaining the lower bound of the left-hand operand. For example:
349
<pre class="programlisting">SELECT array_dims(1 || ARRAY[2,3]);
355
SELECT array_dims(ARRAY[1,2] || 3);
362
<p> When two arrays with an equal number of dimensions are concatenated, the
363
result retains the lower bound subscript of the left-hand operand's outer
364
dimension. The result is an array comprising every element of the left-hand
365
operand followed by every element of the right-hand operand. For example:
367
<pre class="programlisting">SELECT array_dims(ARRAY[1,2] || ARRAY[3,4,5]);
373
SELECT array_dims(ARRAY[[1,2],[3,4]] || ARRAY[[5,6],[7,8],[9,0]]);
380
<p> When an <i class="replaceable"><tt>N</tt></i>-dimensional array is pushed on to the beginning
381
or end of an <i class="replaceable"><tt>N+1</tt></i>-dimensional array, the result is
382
analogous to the element-array case above. Each <i class="replaceable"><tt>N</tt></i>-dimensional
383
sub-array is essentially an element of the <i class="replaceable"><tt>N+1</tt></i>-dimensional
384
array's outer dimension. For example:
386
<pre class="programlisting">SELECT array_dims(ARRAY[1,2] || ARRAY[[3,4],[5,6]]);
393
<p> An array can also be constructed by using the functions
394
<tt class="function">array_prepend</tt>, <tt class="function">array_append</tt>,
395
or <tt class="function">array_cat</tt>. The first two only support one-dimensional
396
arrays, but <tt class="function">array_cat</tt> supports multidimensional arrays.
398
Note that the concatenation operator discussed above is preferred over
399
direct use of these functions. In fact, the functions are primarily for use
400
in implementing the concatenation operator. However, they may be directly
401
useful in the creation of user-defined aggregates. Some examples:
404
<pre class="programlisting">SELECT array_prepend(1, ARRAY[2,3]);
410
SELECT array_append(ARRAY[1,2], 3);
416
SELECT array_cat(ARRAY[1,2], ARRAY[3,4]);
422
SELECT array_cat(ARRAY[[1,2],[3,4]], ARRAY[5,6]);
424
---------------------
428
SELECT array_cat(ARRAY[5,6], ARRAY[[1,2],[3,4]]);
430
---------------------
431
{{5,6},{1,2},{3,4}}</pre>
435
<div class="sect2" lang="en">
436
<div class="titlepage">
437
<div><div><h3 class="title">
438
<a name="id2538219"></a>8.10.5.�Searching in Arrays</h3></div></div>
441
<p> To search for a value in an array, you must check each value of the
442
array. This can be done by hand, if you know the size of the array.
446
<pre class="programlisting">SELECT * FROM sal_emp WHERE pay_by_quarter[1] = 10000 OR
447
pay_by_quarter[2] = 10000 OR
448
pay_by_quarter[3] = 10000 OR
449
pay_by_quarter[4] = 10000;</pre>
452
However, this quickly becomes tedious for large arrays, and is not
453
helpful if the size of the array is uncertain. An alternative method is
454
described in <a href="functions-comparisons.html" title="9.17.�Row and Array Comparisons">Section�9.17, “Row and Array Comparisons”</a>. The above
455
query could be replaced by:
458
<pre class="programlisting">SELECT * FROM sal_emp WHERE 10000 = ANY (pay_by_quarter);</pre>
461
In addition, you could find rows where the array had all values
465
<pre class="programlisting">SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter);</pre>
469
<div class="tip" style="margin-left: 0.5in; margin-right: 0.5in;">
470
<h3 class="title">Tip</h3>
471
<p> Arrays are not sets; searching for specific array elements
472
may be a sign of database misdesign. Consider
473
using a separate table with a row for each item that would be an
474
array element. This will be easier to search, and is likely to
475
scale up better to large numbers of elements.
479
<div class="sect2" lang="en">
480
<div class="titlepage">
481
<div><div><h3 class="title">
482
<a name="id2538270"></a>8.10.6.�Array Input and Output Syntax</h3></div></div>
485
<p> The external text representation of an array value consists of items that
486
are interpreted according to the I/O conversion rules for the array's
487
element type, plus decoration that indicates the array structure.
488
The decoration consists of curly braces (<tt class="literal">{</tt> and <tt class="literal">}</tt>)
489
around the array value plus delimiter characters between adjacent items.
490
The delimiter character is usually a comma (<tt class="literal">,</tt>) but can be
491
something else: it is determined by the <tt class="literal">typdelim</tt> setting
492
for the array's element type. (Among the standard data types provided
493
in the <span class="productname">PostgreSQL</span> distribution, type
494
<tt class="literal">box</tt> uses a semicolon (<tt class="literal">;</tt>) but all the others
495
use comma.) In a multidimensional array, each dimension (row, plane,
496
cube, etc.) gets its own level of curly braces, and delimiters
497
must be written between adjacent curly-braced entities of the same level.
499
<p> The array output routine will put double quotes around element values
500
if they are empty strings or contain curly braces, delimiter characters,
501
double quotes, backslashes, or white space. Double quotes and backslashes
502
embedded in element values will be backslash-escaped. For numeric
503
data types it is safe to assume that double quotes will never appear, but
504
for textual data types one should be prepared to cope with either presence
505
or absence of quotes. (This is a change in behavior from pre-7.2
506
<span class="productname">PostgreSQL</span> releases.)
508
<p> By default, the lower bound index value of an array's dimensions is
509
set to one. If any of an array's dimensions has a lower bound index not
510
equal to one, an additional decoration that indicates the actual
511
array dimensions will precede the array structure decoration.
512
The decoration consists of square braces (<tt class="literal">[</tt> and <tt class="literal">]</tt>)
513
around each array dimension's lower and upper bound indicies, plus
514
a colon (<tt class="literal">:</tt>) delimiter character inbetween. Delimiting the
515
array dimension decoration from the array structure decoration is a
516
single assignment operator (<tt class="literal">=</tt>). For example:
518
<pre class="programlisting">SELECT 1 || ARRAY[2,3] AS array;
525
SELECT ARRAY[1,2] || ARRAY[[3,4]] AS array;
528
--------------------------
529
[0:1][1:2]={{1,2},{3,4}}
533
<p> In a similar fashion, an array with non-default indicies may be specified
534
using the same literal syntax. For example:
536
<pre class="programlisting">SELECT f1[1][-2][3] AS e1, f1[1][-1][5] AS e2
537
FROM (SELECT '[1:1][-2:-1][3:5]={{{1,2,3},{4,5,6}}}'::int[] AS f1) AS ss;
545
<p> As shown previously, when writing an array value you may write double
546
quotes around any individual array element. You <span class="emphasis"><em>must</em></span> do so
547
if the element value would otherwise confuse the array-value parser.
548
For example, elements containing curly braces, commas (or whatever the
549
delimiter character is), double quotes, backslashes, or leading or trailing
550
whitespace must be double-quoted. To put a double quote or backslash in a
551
quoted array element value, precede it with a backslash. Alternatively, you
552
can use backslash-escaping to protect all data characters that would
553
otherwise be taken as array syntax.
555
<p> You may write whitespace before a left brace or after a right
556
brace. You may also write whitespace before or after any individual item
557
string. In all of these cases the whitespace will be ignored. However,
558
whitespace within double quoted elements, or surrounded on both sides by
559
non-whitespace characters of an element, are not ignored.
561
<div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
562
<h3 class="title">Note</h3>
563
<p> Remember that what you write in an SQL command will first be interpreted
564
as a string literal, and then as an array. This doubles the number of
565
backslashes you need. For example, to insert a <tt class="type">text</tt> array
566
value containing a backslash and a double quote, you'd need to write
568
<pre class="programlisting">INSERT ... VALUES ('{"\\\\","\\""}');</pre>
570
The string-literal processor removes one level of backslashes, so that
571
what arrives at the array-value parser looks like <tt class="literal">{"\\","\""}</tt>.
572
In turn, the strings fed to the <tt class="type">text</tt> data type's input routine
573
become <tt class="literal">\</tt> and <tt class="literal">"</tt> respectively. (If we were working
574
with a data type whose input routine also treated backslashes specially,
575
<tt class="type">bytea</tt> for example, we might need as many as eight backslashes
576
in the command to get one backslash into the stored array element.)
579
<div class="tip" style="margin-left: 0.5in; margin-right: 0.5in;">
580
<h3 class="title">Tip</h3>
581
<p> The <tt class="literal">ARRAY</tt> constructor syntax (see
582
<a href="sql-expressions.html#sql-syntax-array-constructors" title="4.2.10.�Array Constructors">Section�4.2.10, “Array Constructors”</a>) is often easier to work
583
with than the array-literal syntax when writing array values in SQL
584
commands. In <tt class="literal">ARRAY</tt>, individual element values are written the
585
same way they would be written when not members of an array.
added
removed
docs/en_US/pg/arrays.html
