7
<primary>array</primary>
11
<productname>PostgreSQL</productname> allows columns of a table to be
12
defined as variable-length multidimensional arrays. Arrays of any
13
built-in or user-defined base type, enum type, or composite type
15
Arrays of domains are not yet supported.
18
<sect2 id="arrays-declaration">
19
<title>Declaration of Array Types</title>
22
<primary>array</primary>
23
<secondary>declaration</secondary>
27
To illustrate the use of array types, we create this table:
29
CREATE TABLE sal_emp (
31
pay_by_quarter integer[],
35
As shown, an array data type is named by appending square brackets
36
(<literal>[]</>) to the data type name of the array elements. The
37
above command will create a table named
38
<structname>sal_emp</structname> with a column of type
39
<type>text</type> (<structfield>name</structfield>), a
40
one-dimensional array of type <type>integer</type>
41
(<structfield>pay_by_quarter</structfield>), which represents the
42
employee's salary by quarter, and a two-dimensional array of
43
<type>text</type> (<structfield>schedule</structfield>), which
44
represents the employee's weekly schedule.
48
The syntax for <command>CREATE TABLE</command> allows the exact size of
49
arrays to be specified, for example:
52
CREATE TABLE tictactoe (
57
However, the current implementation does not enforce the array size
58
limits — the behavior is the same as for arrays of unspecified
63
Actually, the current implementation does not enforce the declared
64
number of dimensions either. Arrays of a particular element type are
65
all considered to be of the same type, regardless of size or number
66
of dimensions. So, declaring number of dimensions or sizes in
67
<command>CREATE TABLE</command> is simply documentation, it does not
68
affect run-time behavior.
72
An alternative syntax, which conforms to the SQL standard, can
73
be used for one-dimensional arrays.
74
<structfield>pay_by_quarter</structfield> could have been defined
77
pay_by_quarter integer ARRAY[4],
79
Or, if no array size is to be specified:
81
pay_by_quarter integer ARRAY,
83
As before, however, <productname>PostgreSQL</> does not enforce the
84
size restriction in any case.
88
<sect2 id="arrays-input">
89
<title>Array Value Input</title>
92
<primary>array</primary>
93
<secondary>constant</secondary>
97
To write an array value as a literal constant, enclose the element
98
values within curly braces and separate them by commas. (If you
99
know C, this is not unlike the C syntax for initializing
100
structures.) You can put double quotes around any element value,
101
and must do so if it contains commas or curly braces. (More
102
details appear below.) Thus, the general format of an array
103
constant is the following:
105
'{ <replaceable>val1</replaceable> <replaceable>delim</replaceable> <replaceable>val2</replaceable> <replaceable>delim</replaceable> ... }'
107
where <replaceable>delim</replaceable> is the delimiter character
108
for the type, as recorded in its <literal>pg_type</literal> entry.
109
Among the standard data types provided in the
110
<productname>PostgreSQL</productname> distribution, type
111
<literal>box</> uses a semicolon (<literal>;</>) but all the others
112
use comma (<literal>,</>). Each <replaceable>val</replaceable> is
113
either a constant of the array element type, or a subarray. An example
114
of an array constant is:
116
'{{1,2,3},{4,5,6},{7,8,9}}'
118
This constant is a two-dimensional, 3-by-3 array consisting of
119
three subarrays of integers.
123
To set an element of an array constant to NULL, write <literal>NULL</>
124
for the element value. (Any upper- or lower-case variant of
125
<literal>NULL</> will do.) If you want an actual string value
126
<quote>NULL</>, you must put double quotes around it.
130
(These kinds of array constants are actually only a special case of
131
the generic type constants discussed in <xref
132
linkend="sql-syntax-constants-generic">. The constant is initially
133
treated as a string and passed to the array input conversion
134
routine. An explicit type specification might be necessary.)
138
Now we can show some <command>INSERT</command> statements:
143
'{10000, 10000, 10000, 10000}',
144
'{{"meeting", "lunch"}, {"training", "presentation"}}');
148
'{20000, 25000, 25000, 25000}',
149
'{{"breakfast", "consulting"}, {"meeting", "lunch"}}');
154
The result of the previous two inserts looks like this:
157
SELECT * FROM sal_emp;
158
name | pay_by_quarter | schedule
159
-------+---------------------------+-------------------------------------------
160
Bill | {10000,10000,10000,10000} | {{meeting,lunch},{training,presentation}}
161
Carol | {20000,25000,25000,25000} | {{breakfast,consulting},{meeting,lunch}}
167
The <literal>ARRAY</> constructor syntax can also be used:
171
ARRAY[10000, 10000, 10000, 10000],
172
ARRAY[['meeting', 'lunch'], ['training', 'presentation']]);
176
ARRAY[20000, 25000, 25000, 25000],
177
ARRAY[['breakfast', 'consulting'], ['meeting', 'lunch']]);
179
Notice that the array elements are ordinary SQL constants or
180
expressions; for instance, string literals are single quoted, instead of
181
double quoted as they would be in an array literal. The <literal>ARRAY</>
182
constructor syntax is discussed in more detail in
183
<xref linkend="sql-syntax-array-constructors">.
187
Multidimensional arrays must have matching extents for each
188
dimension. A mismatch causes an error report, for example:
193
'{10000, 10000, 10000, 10000}',
194
'{{"meeting", "lunch"}, {"meeting"}}');
195
ERROR: multidimensional arrays must have array expressions with matching dimensions
200
<sect2 id="arrays-accessing">
201
<title>Accessing Arrays</title>
204
<primary>array</primary>
205
<secondary>accessing</secondary>
209
Now, we can run some queries on the table.
210
First, we show how to access a single element of an array at a time.
211
This query retrieves the names of the employees whose pay changed in
215
SELECT name FROM sal_emp WHERE pay_by_quarter[1] <> pay_by_quarter[2];
223
The array subscript numbers are written within square brackets.
224
By default <productname>PostgreSQL</productname> uses the
225
one-based numbering convention for arrays, that is,
226
an array of <replaceable>n</> elements starts with <literal>array[1]</literal> and
227
ends with <literal>array[<replaceable>n</>]</literal>.
231
This query retrieves the third quarter pay of all employees:
234
SELECT pay_by_quarter[3] FROM sal_emp;
245
We can also access arbitrary rectangular slices of an array, or
246
subarrays. An array slice is denoted by writing
247
<literal><replaceable>lower-bound</replaceable>:<replaceable>upper-bound</replaceable></literal>
248
for one or more array dimensions. For example, this query retrieves the first
249
item on Bill's schedule for the first two days of the week:
252
SELECT schedule[1:2][1:1] FROM sal_emp WHERE name = 'Bill';
255
------------------------
256
{{meeting},{training}}
260
If any dimension is written as a slice, i.e. contains a colon, then all
261
dimensions are treated as slices. Any dimension that has only a single
262
number (no colon) is treated as being from <literal>1</>
263
to the number specified. For example, <literal>[2]</> is treated as
264
<literal>[1:2]</>, as in this example:
267
SELECT schedule[1:2][2] FROM sal_emp WHERE name = 'Bill';
270
-------------------------------------------
271
{{meeting,lunch},{training,presentation}}
275
To avoid confusion with the non-slice case, it's best to use slice syntax
276
for all dimensions, e.g., <literal>[1:2][1:1]</>, not <literal>[2][1:1]</>.
280
An array subscript expression will return null if either the array itself or
281
any of the subscript expressions are null. Also, null is returned if a
282
subscript is outside the array bounds (this case does not raise an error).
283
For example, if <literal>schedule</>
284
currently has the dimensions <literal>[1:3][1:2]</> then referencing
285
<literal>schedule[3][3]</> yields NULL. Similarly, an array reference
286
with the wrong number of subscripts yields a null rather than an error.
290
An array slice expression likewise yields null if the array itself or
291
any of the subscript expressions are null. However, in other corner
292
cases such as selecting an array slice that
293
is completely outside the current array bounds, a slice expression
294
yields an empty (zero-dimensional) array instead of null. (This
295
does not match non-slice behavior and is done for historical reasons.)
296
If the requested slice partially overlaps the array bounds, then it
297
is silently reduced to just the overlapping region.
301
The current dimensions of any array value can be retrieved with the
302
<function>array_dims</function> function:
305
SELECT array_dims(schedule) FROM sal_emp WHERE name = 'Carol';
313
<function>array_dims</function> produces a <type>text</type> result,
314
which is convenient for people to read but perhaps not so convenient
315
for programs. Dimensions can also be retrieved with
316
<function>array_upper</function> and <function>array_lower</function>,
317
which return the upper and lower bound of a
318
specified array dimension, respectively:
321
SELECT array_upper(schedule, 1) FROM sal_emp WHERE name = 'Carol';
329
<function>array_length</function> will return the length of a specified
333
SELECT array_length(schedule, 1) FROM sal_emp WHERE name = 'Carol';
343
<sect2 id="arrays-modifying">
344
<title>Modifying Arrays</title>
347
<primary>array</primary>
348
<secondary>modifying</secondary>
352
An array value can be replaced completely:
355
UPDATE sal_emp SET pay_by_quarter = '{25000,25000,27000,27000}'
356
WHERE name = 'Carol';
359
or using the <literal>ARRAY</literal> expression syntax:
362
UPDATE sal_emp SET pay_by_quarter = ARRAY[25000,25000,27000,27000]
363
WHERE name = 'Carol';
366
An array can also be updated at a single element:
369
UPDATE sal_emp SET pay_by_quarter[4] = 15000
373
or updated in a slice:
376
UPDATE sal_emp SET pay_by_quarter[1:2] = '{27000,27000}'
377
WHERE name = 'Carol';
383
A stored array value can be enlarged by assigning to element(s) not already
384
present. Any positions between those previously present and the newly
385
assigned element(s) will be filled with nulls. For example, if array
386
<literal>myarray</> currently has 4 elements, it will have six
387
elements after an update that assigns to <literal>myarray[6]</>,
388
and <literal>myarray[5]</> will contain a null.
389
Currently, enlargement in this fashion is only allowed for one-dimensional
390
arrays, not multidimensional arrays.
394
Subscripted assignment allows creation of arrays that do not use one-based
395
subscripts. For example one might assign to <literal>myarray[-2:7]</> to
396
create an array with subscript values running from -2 to 7.
400
New array values can also be constructed by using the concatenation operator,
401
<literal>||</literal>:
403
SELECT ARRAY[1,2] || ARRAY[3,4];
409
SELECT ARRAY[5,6] || ARRAY[[1,2],[3,4]];
411
---------------------
418
The concatenation operator allows a single element to be pushed on to the
419
beginning or end of a one-dimensional array. It also accepts two
420
<replaceable>N</>-dimensional arrays, or an <replaceable>N</>-dimensional
421
and an <replaceable>N+1</>-dimensional array.
425
When a single element is pushed on to either the beginning or end of a
426
one-dimensional array, the result is an array with the same lower bound
427
subscript as the array operand. For example:
429
SELECT array_dims(1 || '[0:1]={2,3}'::int[]);
435
SELECT array_dims(ARRAY[1,2] || 3);
444
When two arrays with an equal number of dimensions are concatenated, the
445
result retains the lower bound subscript of the left-hand operand's outer
446
dimension. The result is an array comprising every element of the left-hand
447
operand followed by every element of the right-hand operand. For example:
449
SELECT array_dims(ARRAY[1,2] || ARRAY[3,4,5]);
455
SELECT array_dims(ARRAY[[1,2],[3,4]] || ARRAY[[5,6],[7,8],[9,0]]);
464
When an <replaceable>N</>-dimensional array is pushed on to the beginning
465
or end of an <replaceable>N+1</>-dimensional array, the result is
466
analogous to the element-array case above. Each <replaceable>N</>-dimensional
467
sub-array is essentially an element of the <replaceable>N+1</>-dimensional
468
array's outer dimension. For example:
470
SELECT array_dims(ARRAY[1,2] || ARRAY[[3,4],[5,6]]);
479
An array can also be constructed by using the functions
480
<function>array_prepend</function>, <function>array_append</function>,
481
or <function>array_cat</function>. The first two only support one-dimensional
482
arrays, but <function>array_cat</function> supports multidimensional arrays.
484
Note that the concatenation operator discussed above is preferred over
485
direct use of these functions. In fact, the functions exist primarily for use
486
in implementing the concatenation operator. However, they might be directly
487
useful in the creation of user-defined aggregates. Some examples:
490
SELECT array_prepend(1, ARRAY[2,3]);
496
SELECT array_append(ARRAY[1,2], 3);
502
SELECT array_cat(ARRAY[1,2], ARRAY[3,4]);
508
SELECT array_cat(ARRAY[[1,2],[3,4]], ARRAY[5,6]);
510
---------------------
514
SELECT array_cat(ARRAY[5,6], ARRAY[[1,2],[3,4]]);
516
---------------------
522
<sect2 id="arrays-searching">
523
<title>Searching in Arrays</title>
526
<primary>array</primary>
527
<secondary>searching</secondary>
531
To search for a value in an array, you must check each value of the
532
array. This can be done by hand, if you know the size of the array.
536
SELECT * FROM sal_emp WHERE pay_by_quarter[1] = 10000 OR
537
pay_by_quarter[2] = 10000 OR
538
pay_by_quarter[3] = 10000 OR
539
pay_by_quarter[4] = 10000;
542
However, this quickly becomes tedious for large arrays, and is not
543
helpful if the size of the array is uncertain. An alternative method is
544
described in <xref linkend="functions-comparisons">. The above
545
query could be replaced by:
548
SELECT * FROM sal_emp WHERE 10000 = ANY (pay_by_quarter);
551
In addition, you could find rows where the array had all values
555
SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter);
561
Alternatively, the <function>generate_subscripts</> function can be used.
566
(SELECT pay_by_quarter,
567
generate_subscripts(pay_by_quarter, 1) AS s
569
WHERE pay_by_quarter[s] = 10000;
572
This function is described in <xref linkend="functions-srf-subscripts">.
577
Arrays are not sets; searching for specific array elements
578
can be a sign of database misdesign. Consider
579
using a separate table with a row for each item that would be an
580
array element. This will be easier to search, and is likely to
581
scale up better to large numbers of elements.
586
<sect2 id="arrays-io">
587
<title>Array Input and Output Syntax</title>
590
<primary>array</primary>
591
<secondary>I/O</secondary>
595
The external text representation of an array value consists of items that
596
are interpreted according to the I/O conversion rules for the array's
597
element type, plus decoration that indicates the array structure.
598
The decoration consists of curly braces (<literal>{</> and <literal>}</>)
599
around the array value plus delimiter characters between adjacent items.
600
The delimiter character is usually a comma (<literal>,</>) but can be
601
something else: it is determined by the <literal>typdelim</> setting
602
for the array's element type. (Among the standard data types provided
603
in the <productname>PostgreSQL</productname> distribution, type
604
<literal>box</> uses a semicolon (<literal>;</>) but all the others
605
use comma.) In a multidimensional array, each dimension (row, plane,
606
cube, etc.) gets its own level of curly braces, and delimiters
607
must be written between adjacent curly-braced entities of the same level.
611
The array output routine will put double quotes around element values
612
if they are empty strings, contain curly braces, delimiter characters,
613
double quotes, backslashes, or white space, or match the word
614
<literal>NULL</>. Double quotes and backslashes
615
embedded in element values will be backslash-escaped. For numeric
616
data types it is safe to assume that double quotes will never appear, but
617
for textual data types one should be prepared to cope with either presence
618
or absence of quotes.
622
By default, the lower bound index value of an array's dimensions is
623
set to one. To represent arrays with other lower bounds, the array
624
subscript ranges can be specified explicitly before writing the
626
This decoration consists of square brackets (<literal>[]</>)
627
around each array dimension's lower and upper bounds, with
628
a colon (<literal>:</>) delimiter character in between. The
629
array dimension decoration is followed by an equal sign (<literal>=</>).
632
SELECT f1[1][-2][3] AS e1, f1[1][-1][5] AS e2
633
FROM (SELECT '[1:1][-2:-1][3:5]={{{1,2,3},{4,5,6}}}'::int[] AS f1) AS ss;
640
The array output routine will include explicit dimensions in its result
641
only when there are one or more lower bounds different from one.
645
If the value written for an element is <literal>NULL</> (in any case
646
variant), the element is taken to be NULL. The presence of any quotes
647
or backslashes disables this and allows the literal string value
648
<quote>NULL</> to be entered. Also, for backwards compatibility with
649
pre-8.2 versions of <productname>PostgreSQL</>, the <xref
650
linkend="guc-array-nulls"> configuration parameter might be turned
651
<literal>off</> to suppress recognition of <literal>NULL</> as a NULL.
655
As shown previously, when writing an array value you can write double
656
quotes around any individual array element. You <emphasis>must</> do so
657
if the element value would otherwise confuse the array-value parser.
658
For example, elements containing curly braces, commas (or whatever the
659
delimiter character is), double quotes, backslashes, or leading or trailing
660
whitespace must be double-quoted. Empty strings and strings matching the
661
word <literal>NULL</> must be quoted, too. To put a double quote or
662
backslash in a quoted array element value, use escape string syntax
663
and precede it with a backslash. Alternatively, you can use
664
backslash-escaping to protect all data characters that would otherwise
665
be taken as array syntax.
669
You can write whitespace before a left brace or after a right
670
brace. You can also write whitespace before or after any individual item
671
string. In all of these cases the whitespace will be ignored. However,
672
whitespace within double-quoted elements, or surrounded on both sides by
673
non-whitespace characters of an element, is not ignored.
678
Remember that what you write in an SQL command will first be interpreted
679
as a string literal, and then as an array. This doubles the number of
680
backslashes you need. For example, to insert a <type>text</> array
681
value containing a backslash and a double quote, you'd need to write:
683
INSERT ... VALUES (E'{"\\\\","\\""}');
685
The escape string processor removes one level of backslashes, so that
686
what arrives at the array-value parser looks like <literal>{"\\","\""}</>.
687
In turn, the strings fed to the <type>text</> data type's input routine
688
become <literal>\</> and <literal>"</> respectively. (If we were working
689
with a data type whose input routine also treated backslashes specially,
690
<type>bytea</> for example, we might need as many as eight backslashes
691
in the command to get one backslash into the stored array element.)
692
Dollar quoting (see <xref linkend="sql-syntax-dollar-quoting">) can be
693
used to avoid the need to double backslashes.
699
The <literal>ARRAY</> constructor syntax (see
700
<xref linkend="sql-syntax-array-constructors">) is often easier to work
701
with than the array-literal syntax when writing array values in SQL
702
commands. In <literal>ARRAY</>, individual element values are written the
703
same way they would be written when not members of an array.
added
removed
doc/src/sgml/array.sgml
