1 <!-- $PostgreSQL: pgsql/doc/src/sgml/array.sgml,v 1.66 2008/04/28 14:48:57 alvherre Exp $ -->
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 This syntax requires an integer constant to denote the array size.
80 As before, however, <productname>PostgreSQL</> does not enforce the
85 <sect2 id="arrays-input">
86 <title>Array Value Input</title>
89 <primary>array</primary>
90 <secondary>constant</secondary>
94 To write an array value as a literal constant, enclose the element
95 values within curly braces and separate them by commas. (If you
96 know C, this is not unlike the C syntax for initializing
97 structures.) You can put double quotes around any element value,
98 and must do so if it contains commas or curly braces. (More
99 details appear below.) Thus, the general format of an array
100 constant is the following:
102 '{ <replaceable>val1</replaceable> <replaceable>delim</replaceable> <replaceable>val2</replaceable> <replaceable>delim</replaceable> ... }'
104 where <replaceable>delim</replaceable> is the delimiter character
105 for the type, as recorded in its <literal>pg_type</literal> entry.
106 Among the standard data types provided in the
107 <productname>PostgreSQL</productname> distribution, type
108 <literal>box</> uses a semicolon (<literal>;</>) but all the others
109 use comma (<literal>,</>). Each <replaceable>val</replaceable> is
110 either a constant of the array element type, or a subarray. An example
111 of an array constant is:
113 '{{1,2,3},{4,5,6},{7,8,9}}'
115 This constant is a two-dimensional, 3-by-3 array consisting of
116 three subarrays of integers.
120 To set an element of an array constant to NULL, write <literal>NULL</>
121 for the element value. (Any upper- or lower-case variant of
122 <literal>NULL</> will do.) If you want an actual string value
123 <quote>NULL</>, you must put double quotes around it.
127 (These kinds of array constants are actually only a special case of
128 the generic type constants discussed in <xref
129 linkend="sql-syntax-constants-generic">. The constant is initially
130 treated as a string and passed to the array input conversion
131 routine. An explicit type specification might be necessary.)
135 Now we can show some <command>INSERT</command> statements:
140 '{10000, 10000, 10000, 10000}',
141 '{{"meeting", "lunch"}, {"training", "presentation"}}');
145 '{20000, 25000, 25000, 25000}',
146 '{{"breakfast", "consulting"}, {"meeting", "lunch"}}');
151 The result of the previous two inserts looks like this:
154 SELECT * FROM sal_emp;
155 name | pay_by_quarter | schedule
156 -------+---------------------------+-------------------------------------------
157 Bill | {10000,10000,10000,10000} | {{meeting,lunch},{training,presentation}}
158 Carol | {20000,25000,25000,25000} | {{breakfast,consulting},{meeting,lunch}}
164 The <literal>ARRAY</> constructor syntax can also be used:
168 ARRAY[10000, 10000, 10000, 10000],
169 ARRAY[['meeting', 'lunch'], ['training', 'presentation']]);
173 ARRAY[20000, 25000, 25000, 25000],
174 ARRAY[['breakfast', 'consulting'], ['meeting', 'lunch']]);
176 Notice that the array elements are ordinary SQL constants or
177 expressions; for instance, string literals are single quoted, instead of
178 double quoted as they would be in an array literal. The <literal>ARRAY</>
179 constructor syntax is discussed in more detail in
180 <xref linkend="sql-syntax-array-constructors">.
184 Multidimensional arrays must have matching extents for each
185 dimension. A mismatch causes an error report, for example:
190 '{10000, 10000, 10000, 10000}',
191 '{{"meeting", "lunch"}, {"meeting"}}');
192 ERROR: multidimensional arrays must have array expressions with matching dimensions
197 <sect2 id="arrays-accessing">
198 <title>Accessing Arrays</title>
201 <primary>array</primary>
202 <secondary>accessing</secondary>
206 Now, we can run some queries on the table.
207 First, we show how to access a single element of an array at a time.
208 This query retrieves the names of the employees whose pay changed in
212 SELECT name FROM sal_emp WHERE pay_by_quarter[1] <> pay_by_quarter[2];
220 The array subscript numbers are written within square brackets.
221 By default <productname>PostgreSQL</productname> uses the
222 one-based numbering convention for arrays, that is,
223 an array of <replaceable>n</> elements starts with <literal>array[1]</literal> and
224 ends with <literal>array[<replaceable>n</>]</literal>.
228 This query retrieves the third quarter pay of all employees:
231 SELECT pay_by_quarter[3] FROM sal_emp;
242 We can also access arbitrary rectangular slices of an array, or
243 subarrays. An array slice is denoted by writing
244 <literal><replaceable>lower-bound</replaceable>:<replaceable>upper-bound</replaceable></literal>
245 for one or more array dimensions. For example, this query retrieves the first
246 item on Bill's schedule for the first two days of the week:
249 SELECT schedule[1:2][1:1] FROM sal_emp WHERE name = 'Bill';
252 ------------------------
253 {{meeting},{training}}
257 If any dimension is written as a slice, i.e. contains a colon, then all
258 dimensions are treated as slices. Any dimension that has only a single
259 number (no colon) is treated as being from <literal>1</>
260 to the number specified. For example, <literal>[2]</> is treated as
261 <literal>[1:2]</>, as in this example:
264 SELECT schedule[1:2][2] FROM sal_emp WHERE name = 'Bill';
267 -------------------------------------------
268 {{meeting,lunch},{training,presentation}}
272 To avoid confusion with the non-slice case, it's best to use slice syntax
273 for all dimensions, e.g., <literal>[1:2][1:1]</>, not <literal>[2][1:1]</>.
277 An array subscript expression will return null if either the array itself or
278 any of the subscript expressions are null. Also, null is returned if a
279 subscript is outside the array bounds (this case does not raise an error).
280 For example, if <literal>schedule</>
281 currently has the dimensions <literal>[1:3][1:2]</> then referencing
282 <literal>schedule[3][3]</> yields NULL. Similarly, an array reference
283 with the wrong number of subscripts yields a null rather than an error.
287 An array slice expression likewise yields null if the array itself or
288 any of the subscript expressions are null. However, in other corner
289 cases such as selecting an array slice that
290 is completely outside the current array bounds, a slice expression
291 yields an empty (zero-dimensional) array instead of null. (This
292 does not match non-slice behavior and is done for historical reasons.)
293 If the requested slice partially overlaps the array bounds, then it
294 is silently reduced to just the overlapping region.
298 The current dimensions of any array value can be retrieved with the
299 <function>array_dims</function> function:
302 SELECT array_dims(schedule) FROM sal_emp WHERE name = 'Carol';
310 <function>array_dims</function> produces a <type>text</type> result,
311 which is convenient for people to read but perhaps not so convenient
312 for programs. Dimensions can also be retrieved with
313 <function>array_upper</function> and <function>array_lower</function>,
314 which return the upper and lower bound of a
315 specified array dimension, respectively:
318 SELECT array_upper(schedule, 1) FROM sal_emp WHERE name = 'Carol';
328 <sect2 id="arrays-modifying">
329 <title>Modifying Arrays</title>
332 <primary>array</primary>
333 <secondary>modifying</secondary>
337 An array value can be replaced completely:
340 UPDATE sal_emp SET pay_by_quarter = '{25000,25000,27000,27000}'
341 WHERE name = 'Carol';
344 or using the <literal>ARRAY</literal> expression syntax:
347 UPDATE sal_emp SET pay_by_quarter = ARRAY[25000,25000,27000,27000]
348 WHERE name = 'Carol';
351 An array can also be updated at a single element:
354 UPDATE sal_emp SET pay_by_quarter[4] = 15000
358 or updated in a slice:
361 UPDATE sal_emp SET pay_by_quarter[1:2] = '{27000,27000}'
362 WHERE name = 'Carol';
368 A stored array value can be enlarged by assigning to element(s) not already
369 present. Any positions between those previously present and the newly
370 assigned element(s) will be filled with nulls. For example, if array
371 <literal>myarray</> currently has 4 elements, it will have six
372 elements after an update that assigns to <literal>myarray[6]</>,
373 and <literal>myarray[5]</> will contain a null.
374 Currently, enlargement in this fashion is only allowed for one-dimensional
375 arrays, not multidimensional arrays.
379 Subscripted assignment allows creation of arrays that do not use one-based
380 subscripts. For example one might assign to <literal>myarray[-2:7]</> to
381 create an array with subscript values running from -2 to 7.
385 New array values can also be constructed by using the concatenation operator,
386 <literal>||</literal>:
388 SELECT ARRAY[1,2] || ARRAY[3,4];
394 SELECT ARRAY[5,6] || ARRAY[[1,2],[3,4]];
396 ---------------------
403 The concatenation operator allows a single element to be pushed on to the
404 beginning or end of a one-dimensional array. It also accepts two
405 <replaceable>N</>-dimensional arrays, or an <replaceable>N</>-dimensional
406 and an <replaceable>N+1</>-dimensional array.
410 When a single element is pushed on to either the beginning or end of a
411 one-dimensional array, the result is an array with the same lower bound
412 subscript as the array operand. For example:
414 SELECT array_dims(1 || '[0:1]={2,3}'::int[]);
420 SELECT array_dims(ARRAY[1,2] || 3);
429 When two arrays with an equal number of dimensions are concatenated, the
430 result retains the lower bound subscript of the left-hand operand's outer
431 dimension. The result is an array comprising every element of the left-hand
432 operand followed by every element of the right-hand operand. For example:
434 SELECT array_dims(ARRAY[1,2] || ARRAY[3,4,5]);
440 SELECT array_dims(ARRAY[[1,2],[3,4]] || ARRAY[[5,6],[7,8],[9,0]]);
449 When an <replaceable>N</>-dimensional array is pushed on to the beginning
450 or end of an <replaceable>N+1</>-dimensional array, the result is
451 analogous to the element-array case above. Each <replaceable>N</>-dimensional
452 sub-array is essentially an element of the <replaceable>N+1</>-dimensional
453 array's outer dimension. For example:
455 SELECT array_dims(ARRAY[1,2] || ARRAY[[3,4],[5,6]]);
464 An array can also be constructed by using the functions
465 <function>array_prepend</function>, <function>array_append</function>,
466 or <function>array_cat</function>. The first two only support one-dimensional
467 arrays, but <function>array_cat</function> supports multidimensional arrays.
469 Note that the concatenation operator discussed above is preferred over
470 direct use of these functions. In fact, the functions exist primarily for use
471 in implementing the concatenation operator. However, they might be directly
472 useful in the creation of user-defined aggregates. Some examples:
475 SELECT array_prepend(1, ARRAY[2,3]);
481 SELECT array_append(ARRAY[1,2], 3);
487 SELECT array_cat(ARRAY[1,2], ARRAY[3,4]);
493 SELECT array_cat(ARRAY[[1,2],[3,4]], ARRAY[5,6]);
495 ---------------------
499 SELECT array_cat(ARRAY[5,6], ARRAY[[1,2],[3,4]]);
501 ---------------------
507 <sect2 id="arrays-searching">
508 <title>Searching in Arrays</title>
511 <primary>array</primary>
512 <secondary>searching</secondary>
516 To search for a value in an array, you must check each value of the
517 array. This can be done by hand, if you know the size of the array.
521 SELECT * FROM sal_emp WHERE pay_by_quarter[1] = 10000 OR
522 pay_by_quarter[2] = 10000 OR
523 pay_by_quarter[3] = 10000 OR
524 pay_by_quarter[4] = 10000;
527 However, this quickly becomes tedious for large arrays, and is not
528 helpful if the size of the array is uncertain. An alternative method is
529 described in <xref linkend="functions-comparisons">. The above
530 query could be replaced by:
533 SELECT * FROM sal_emp WHERE 10000 = ANY (pay_by_quarter);
536 In addition, you could find rows where the array had all values
540 SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter);
546 Alternatively, the <function>generate_subscripts</> function can be used.
551 (SELECT pay_by_quarter,
552 generate_subscripts(pay_by_quarter, 1) AS s
554 WHERE pay_by_quarter[s] = 10000;
557 This function is described in <xref linkend="functions-srf-subscripts">.
562 Arrays are not sets; searching for specific array elements
563 can be a sign of database misdesign. Consider
564 using a separate table with a row for each item that would be an
565 array element. This will be easier to search, and is likely to
566 scale up better to large numbers of elements.
571 <sect2 id="arrays-io">
572 <title>Array Input and Output Syntax</title>
575 <primary>array</primary>
576 <secondary>I/O</secondary>
580 The external text representation of an array value consists of items that
581 are interpreted according to the I/O conversion rules for the array's
582 element type, plus decoration that indicates the array structure.
583 The decoration consists of curly braces (<literal>{</> and <literal>}</>)
584 around the array value plus delimiter characters between adjacent items.
585 The delimiter character is usually a comma (<literal>,</>) but can be
586 something else: it is determined by the <literal>typdelim</> setting
587 for the array's element type. (Among the standard data types provided
588 in the <productname>PostgreSQL</productname> distribution, type
589 <literal>box</> uses a semicolon (<literal>;</>) but all the others
590 use comma.) In a multidimensional array, each dimension (row, plane,
591 cube, etc.) gets its own level of curly braces, and delimiters
592 must be written between adjacent curly-braced entities of the same level.
596 The array output routine will put double quotes around element values
597 if they are empty strings, contain curly braces, delimiter characters,
598 double quotes, backslashes, or white space, or match the word
599 <literal>NULL</>. Double quotes and backslashes
600 embedded in element values will be backslash-escaped. For numeric
601 data types it is safe to assume that double quotes will never appear, but
602 for textual data types one should be prepared to cope with either presence
603 or absence of quotes.
607 By default, the lower bound index value of an array's dimensions is
608 set to one. To represent arrays with other lower bounds, the array
609 subscript ranges can be specified explicitly before writing the
611 This decoration consists of square brackets (<literal>[]</>)
612 around each array dimension's lower and upper bounds, with
613 a colon (<literal>:</>) delimiter character in between. The
614 array dimension decoration is followed by an equal sign (<literal>=</>).
617 SELECT f1[1][-2][3] AS e1, f1[1][-1][5] AS e2
618 FROM (SELECT '[1:1][-2:-1][3:5]={{{1,2,3},{4,5,6}}}'::int[] AS f1) AS ss;
625 The array output routine will include explicit dimensions in its result
626 only when there are one or more lower bounds different from one.
630 If the value written for an element is <literal>NULL</> (in any case
631 variant), the element is taken to be NULL. The presence of any quotes
632 or backslashes disables this and allows the literal string value
633 <quote>NULL</> to be entered. Also, for backwards compatibility with
634 pre-8.2 versions of <productname>PostgreSQL</>, the <xref
635 linkend="guc-array-nulls"> configuration parameter might be turned
636 <literal>off</> to suppress recognition of <literal>NULL</> as a NULL.
640 As shown previously, when writing an array value you can write double
641 quotes around any individual array element. You <emphasis>must</> do so
642 if the element value would otherwise confuse the array-value parser.
643 For example, elements containing curly braces, commas (or whatever the
644 delimiter character is), double quotes, backslashes, or leading or trailing
645 whitespace must be double-quoted. Empty strings and strings matching the
646 word <literal>NULL</> must be quoted, too. To put a double quote or
647 backslash in a quoted array element value, use escape string syntax
648 and precede it with a backslash. Alternatively, you can use
649 backslash-escaping to protect all data characters that would otherwise
650 be taken as array syntax.
654 You can write whitespace before a left brace or after a right
655 brace. You can also write whitespace before or after any individual item
656 string. In all of these cases the whitespace will be ignored. However,
657 whitespace within double-quoted elements, or surrounded on both sides by
658 non-whitespace characters of an element, is not ignored.
663 Remember that what you write in an SQL command will first be interpreted
664 as a string literal, and then as an array. This doubles the number of
665 backslashes you need. For example, to insert a <type>text</> array
666 value containing a backslash and a double quote, you'd need to write:
668 INSERT ... VALUES (E'{"\\\\","\\""}');
670 The escape string processor removes one level of backslashes, so that
671 what arrives at the array-value parser looks like <literal>{"\\","\""}</>.
672 In turn, the strings fed to the <type>text</> data type's input routine
673 become <literal>\</> and <literal>"</> respectively. (If we were working
674 with a data type whose input routine also treated backslashes specially,
675 <type>bytea</> for example, we might need as many as eight backslashes
676 in the command to get one backslash into the stored array element.)
677 Dollar quoting (see <xref linkend="sql-syntax-dollar-quoting">) can be
678 used to avoid the need to double backslashes.
684 The <literal>ARRAY</> constructor syntax (see
685 <xref linkend="sql-syntax-array-constructors">) is often easier to work
686 with than the array-literal syntax when writing array values in SQL
687 commands. In <literal>ARRAY</>, individual element values are written the
688 same way they would be written when not members of an array.