1 <!-- doc/src/sgml/array.sgml -->
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 ignores any supplied array size
58 limits, i.e., the behavior is the same as for arrays of unspecified
63 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 the array size or number of dimensions 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 by using
73 the keyword <literal>ARRAY</>, can 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, all use a comma
111 (<literal>,</>), except for type <type>box</> which uses a semicolon
112 (<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 Multidimensional arrays must have matching extents for each
168 dimension. A mismatch causes an error, for example:
173 '{10000, 10000, 10000, 10000}',
174 '{{"meeting", "lunch"}, {"meeting"}}');
175 ERROR: multidimensional arrays must have array expressions with matching dimensions
180 The <literal>ARRAY</> constructor syntax can also be used:
184 ARRAY[10000, 10000, 10000, 10000],
185 ARRAY[['meeting', 'lunch'], ['training', 'presentation']]);
189 ARRAY[20000, 25000, 25000, 25000],
190 ARRAY[['breakfast', 'consulting'], ['meeting', 'lunch']]);
192 Notice that the array elements are ordinary SQL constants or
193 expressions; for instance, string literals are single quoted, instead of
194 double quoted as they would be in an array literal. The <literal>ARRAY</>
195 constructor syntax is discussed in more detail in
196 <xref linkend="sql-syntax-array-constructors">.
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.
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 a
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 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
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 instead of
302 The current dimensions of any array value can be retrieved with the
303 <function>array_dims</function> function:
306 SELECT array_dims(schedule) FROM sal_emp WHERE name = 'Carol';
314 <function>array_dims</function> produces a <type>text</type> result,
315 which is convenient for people to read but perhaps inconvenient
316 for programs. Dimensions can also be retrieved with
317 <function>array_upper</function> and <function>array_lower</function>,
318 which return the upper and lower bound of a
319 specified array dimension, respectively:
322 SELECT array_upper(schedule, 1) FROM sal_emp WHERE name = 'Carol';
330 <function>array_length</function> will return the length of a specified
334 SELECT array_length(schedule, 1) FROM sal_emp WHERE name = 'Carol';
342 <function>cardinality</function> returns the total number of elements in an
343 array across all dimensions. It is effectively the number of rows a call to
344 <function>unnest</function> would yield:
347 SELECT cardinality(schedule) FROM sal_emp WHERE name = 'Carol';
357 <sect2 id="arrays-modifying">
358 <title>Modifying Arrays</title>
361 <primary>array</primary>
362 <secondary>modifying</secondary>
366 An array value can be replaced completely:
369 UPDATE sal_emp SET pay_by_quarter = '{25000,25000,27000,27000}'
370 WHERE name = 'Carol';
373 or using the <literal>ARRAY</literal> expression syntax:
376 UPDATE sal_emp SET pay_by_quarter = ARRAY[25000,25000,27000,27000]
377 WHERE name = 'Carol';
380 An array can also be updated at a single element:
383 UPDATE sal_emp SET pay_by_quarter[4] = 15000
387 or updated in a slice:
390 UPDATE sal_emp SET pay_by_quarter[1:2] = '{27000,27000}'
391 WHERE name = 'Carol';
397 A stored array value can be enlarged by assigning to elements not already
398 present. Any positions between those previously present and the newly
399 assigned elements will be filled with nulls. For example, if array
400 <literal>myarray</> currently has 4 elements, it will have six
401 elements after an update that assigns to <literal>myarray[6]</>;
402 <literal>myarray[5]</> will contain null.
403 Currently, enlargement in this fashion is only allowed for one-dimensional
404 arrays, not multidimensional arrays.
408 Subscripted assignment allows creation of arrays that do not use one-based
409 subscripts. For example one might assign to <literal>myarray[-2:7]</> to
410 create an array with subscript values from -2 to 7.
414 New array values can also be constructed using the concatenation operator,
415 <literal>||</literal>:
417 SELECT ARRAY[1,2] || ARRAY[3,4];
423 SELECT ARRAY[5,6] || ARRAY[[1,2],[3,4]];
425 ---------------------
432 The concatenation operator allows a single element to be pushed onto the
433 beginning or end of a one-dimensional array. It also accepts two
434 <replaceable>N</>-dimensional arrays, or an <replaceable>N</>-dimensional
435 and an <replaceable>N+1</>-dimensional array.
439 When a single element is pushed onto either the beginning or end of a
440 one-dimensional array, the result is an array with the same lower bound
441 subscript as the array operand. For example:
443 SELECT array_dims(1 || '[0:1]={2,3}'::int[]);
449 SELECT array_dims(ARRAY[1,2] || 3);
458 When two arrays with an equal number of dimensions are concatenated, the
459 result retains the lower bound subscript of the left-hand operand's outer
460 dimension. The result is an array comprising every element of the left-hand
461 operand followed by every element of the right-hand operand. For example:
463 SELECT array_dims(ARRAY[1,2] || ARRAY[3,4,5]);
469 SELECT array_dims(ARRAY[[1,2],[3,4]] || ARRAY[[5,6],[7,8],[9,0]]);
478 When an <replaceable>N</>-dimensional array is pushed onto the beginning
479 or end of an <replaceable>N+1</>-dimensional array, the result is
480 analogous to the element-array case above. Each <replaceable>N</>-dimensional
481 sub-array is essentially an element of the <replaceable>N+1</>-dimensional
482 array's outer dimension. For example:
484 SELECT array_dims(ARRAY[1,2] || ARRAY[[3,4],[5,6]]);
493 An array can also be constructed by using the functions
494 <function>array_prepend</function>, <function>array_append</function>,
495 or <function>array_cat</function>. The first two only support one-dimensional
496 arrays, but <function>array_cat</function> supports multidimensional arrays.
498 Note that the concatenation operator discussed above is preferred over
499 direct use of these functions. In fact, these functions primarily exist for use
500 in implementing the concatenation operator. However, they might be directly
501 useful in the creation of user-defined aggregates. Some examples:
504 SELECT array_prepend(1, ARRAY[2,3]);
510 SELECT array_append(ARRAY[1,2], 3);
516 SELECT array_cat(ARRAY[1,2], ARRAY[3,4]);
522 SELECT array_cat(ARRAY[[1,2],[3,4]], ARRAY[5,6]);
524 ---------------------
528 SELECT array_cat(ARRAY[5,6], ARRAY[[1,2],[3,4]]);
530 ---------------------
536 <sect2 id="arrays-searching">
537 <title>Searching in Arrays</title>
540 <primary>array</primary>
541 <secondary>searching</secondary>
545 To search for a value in an array, each value must be checked.
546 This can be done manually, if you know the size of the array.
550 SELECT * FROM sal_emp WHERE pay_by_quarter[1] = 10000 OR
551 pay_by_quarter[2] = 10000 OR
552 pay_by_quarter[3] = 10000 OR
553 pay_by_quarter[4] = 10000;
556 However, this quickly becomes tedious for large arrays, and is not
557 helpful if the size of the array is unknown. An alternative method is
558 described in <xref linkend="functions-comparisons">. The above
559 query could be replaced by:
562 SELECT * FROM sal_emp WHERE 10000 = ANY (pay_by_quarter);
565 In addition, you can find rows where the array has all values
569 SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter);
575 Alternatively, the <function>generate_subscripts</> function can be used.
580 (SELECT pay_by_quarter,
581 generate_subscripts(pay_by_quarter, 1) AS s
583 WHERE pay_by_quarter[s] = 10000;
586 This function is described in <xref linkend="functions-srf-subscripts">.
590 You can also search an array using the <literal>&&</> operator,
591 which checks whether the left operand overlaps with the right operand.
595 SELECT * FROM sal_emp WHERE pay_by_quarter && ARRAY[10000];
598 This and other array operators are further described in
599 <xref linkend="functions-array">. It can be accelerated by an appropriate
600 index, as described in <xref linkend="indexes-types">.
605 Arrays are not sets; searching for specific array elements
606 can be a sign of database misdesign. Consider
607 using a separate table with a row for each item that would be an
608 array element. This will be easier to search, and is likely to
609 scale better for a large number of elements.
614 <sect2 id="arrays-io">
615 <title>Array Input and Output Syntax</title>
618 <primary>array</primary>
619 <secondary>I/O</secondary>
623 The external text representation of an array value consists of items that
624 are interpreted according to the I/O conversion rules for the array's
625 element type, plus decoration that indicates the array structure.
626 The decoration consists of curly braces (<literal>{</> and <literal>}</>)
627 around the array value plus delimiter characters between adjacent items.
628 The delimiter character is usually a comma (<literal>,</>) but can be
629 something else: it is determined by the <literal>typdelim</> setting
630 for the array's element type. Among the standard data types provided
631 in the <productname>PostgreSQL</productname> distribution, all use a comma,
632 except for type <type>box</>, which uses a semicolon (<literal>;</>).
633 In a multidimensional array, each dimension (row, plane,
634 cube, etc.) gets its own level of curly braces, and delimiters
635 must be written between adjacent curly-braced entities of the same level.
639 The array output routine will put double quotes around element values
640 if they are empty strings, contain curly braces, delimiter characters,
641 double quotes, backslashes, or white space, or match the word
642 <literal>NULL</>. Double quotes and backslashes
643 embedded in element values will be backslash-escaped. For numeric
644 data types it is safe to assume that double quotes will never appear, but
645 for textual data types one should be prepared to cope with either the presence
646 or absence of quotes.
650 By default, the lower bound index value of an array's dimensions is
651 set to one. To represent arrays with other lower bounds, the array
652 subscript ranges can be specified explicitly before writing the
654 This decoration consists of square brackets (<literal>[]</>)
655 around each array dimension's lower and upper bounds, with
656 a colon (<literal>:</>) delimiter character in between. The
657 array dimension decoration is followed by an equal sign (<literal>=</>).
660 SELECT f1[1][-2][3] AS e1, f1[1][-1][5] AS e2
661 FROM (SELECT '[1:1][-2:-1][3:5]={{{1,2,3},{4,5,6}}}'::int[] AS f1) AS ss;
668 The array output routine will include explicit dimensions in its result
669 only when there are one or more lower bounds different from one.
673 If the value written for an element is <literal>NULL</> (in any case
674 variant), the element is taken to be NULL. The presence of any quotes
675 or backslashes disables this and allows the literal string value
676 <quote>NULL</> to be entered. Also, for backward compatibility with
677 pre-8.2 versions of <productname>PostgreSQL</>, the <xref
678 linkend="guc-array-nulls"> configuration parameter can be turned
679 <literal>off</> to suppress recognition of <literal>NULL</> as a NULL.
683 As shown previously, when writing an array value you can use double
684 quotes around any individual array element. You <emphasis>must</> do so
685 if the element value would otherwise confuse the array-value parser.
686 For example, elements containing curly braces, commas (or the data type's
687 delimiter character), double quotes, backslashes, or leading or trailing
688 whitespace must be double-quoted. Empty strings and strings matching the
689 word <literal>NULL</> must be quoted, too. To put a double quote or
690 backslash in a quoted array element value, use escape string syntax
691 and precede it with a backslash. Alternatively, you can avoid quotes and use
692 backslash-escaping to protect all data characters that would otherwise
693 be taken as array syntax.
697 You can add whitespace before a left brace or after a right
698 brace. You can also add whitespace before or after any individual item
699 string. In all of these cases the whitespace will be ignored. However,
700 whitespace within double-quoted elements, or surrounded on both sides by
701 non-whitespace characters of an element, is not ignored.
706 Remember that what you write in an SQL command will first be interpreted
707 as a string literal, and then as an array. This doubles the number of
708 backslashes you need. For example, to insert a <type>text</> array
709 value containing a backslash and a double quote, you'd need to write:
711 INSERT ... VALUES (E'{"\\\\","\\""}');
713 The escape string processor removes one level of backslashes, so that
714 what arrives at the array-value parser looks like <literal>{"\\","\""}</>.
715 In turn, the strings fed to the <type>text</> data type's input routine
716 become <literal>\</> and <literal>"</> respectively. (If we were working
717 with a data type whose input routine also treated backslashes specially,
718 <type>bytea</> for example, we might need as many as eight backslashes
719 in the command to get one backslash into the stored array element.)
720 Dollar quoting (see <xref linkend="sql-syntax-dollar-quoting">) can be
721 used to avoid the need to double backslashes.
727 The <literal>ARRAY</> constructor syntax (see
728 <xref linkend="sql-syntax-array-constructors">) is often easier to work
729 with than the array-literal syntax when writing array values in SQL
730 commands. In <literal>ARRAY</>, individual element values are written the
731 same way they would be written when not members of an array.