1 <!-- $PostgreSQL: pgsql/doc/src/sgml/array.sgml,v 1.39 2004/11/15 06:32:13 neilc 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 can be created. (Arrays of
14 composite types or domains are not yet supported, however.)
18 <title>Declaration of Array Types</title>
21 To illustrate the use of array types, we create this table:
23 CREATE TABLE sal_emp (
25 pay_by_quarter integer[],
29 As shown, an array data type is named by appending square brackets
30 (<literal>[]</>) to the data type name of the array elements. The
31 above command will create a table named
32 <structname>sal_emp</structname> with a column of type
33 <type>text</type> (<structfield>name</structfield>), a
34 one-dimensional array of type <type>integer</type>
35 (<structfield>pay_by_quarter</structfield>), which represents the
36 employee's salary by quarter, and a two-dimensional array of
37 <type>text</type> (<structfield>schedule</structfield>), which
38 represents the employee's weekly schedule.
42 The syntax for <command>CREATE TABLE</command> allows the exact size of
43 arrays to be specified, for example:
46 CREATE TABLE tictactoe (
51 However, the current implementation does not enforce the array size
52 limits — the behavior is the same as for arrays of unspecified
57 Actually, the current implementation does not enforce the declared
58 number of dimensions either. Arrays of a particular element type are
59 all considered to be of the same type, regardless of size or number
60 of dimensions. So, declaring number of dimensions or sizes in
61 <command>CREATE TABLE</command> is simply documentation, it does not
62 affect runtime behavior.
66 An alternative, SQL99-standard syntax may be used for one-dimensional arrays.
67 <structfield>pay_by_quarter</structfield> could have been defined as:
69 pay_by_quarter integer ARRAY[4],
71 This syntax requires an integer constant to denote the array size.
72 As before, however, <productname>PostgreSQL</> does not enforce the
78 <title>Array Value Input</title>
81 <primary>array</primary>
82 <secondary>constant</secondary>
86 To write an array value as a literal constant, enclose the element
87 values within curly braces and separate them by commas. (If you
88 know C, this is not unlike the C syntax for initializing
89 structures.) You may put double quotes around any element value,
90 and must do so if it contains commas or curly braces. (More
91 details appear below.) Thus, the general format of an array
92 constant is the following:
94 '{ <replaceable>val1</replaceable> <replaceable>delim</replaceable> <replaceable>val2</replaceable> <replaceable>delim</replaceable> ... }'
96 where <replaceable>delim</replaceable> is the delimiter character
97 for the type, as recorded in its <literal>pg_type</literal> entry.
98 Among the standard data types provided in the
99 <productname>PostgreSQL</productname> distribution, type
100 <literal>box</> uses a semicolon (<literal>;</>) but all the others
101 use comma (<literal>,</>). Each <replaceable>val</replaceable> is
102 either a constant of the array element type, or a subarray. An example
103 of an array constant is
105 '{{1,2,3},{4,5,6},{7,8,9}}'
107 This constant is a two-dimensional, 3-by-3 array consisting of
108 three subarrays of integers.
112 (These kinds of array constants are actually only a special case of
113 the generic type constants discussed in <xref
114 linkend="sql-syntax-constants-generic">. The constant is initially
115 treated as a string and passed to the array input conversion
116 routine. An explicit type specification might be necessary.)
120 Now we can show some <command>INSERT</command> statements.
125 '{10000, 10000, 10000, 10000}',
126 '{{"meeting", "lunch"}, {"meeting"}}');
127 ERROR: multidimensional arrays must have array expressions with matching dimensions
130 Note that multidimensional arrays must have matching extents for each
131 dimension. A mismatch causes an error report.
136 '{10000, 10000, 10000, 10000}',
137 '{{"meeting", "lunch"}, {"training", "presentation"}}');
141 '{20000, 25000, 25000, 25000}',
142 '{{"breakfast", "consulting"}, {"meeting", "lunch"}}');
147 A limitation of the present array implementation is that individual
148 elements of an array cannot be SQL null values. The entire array
149 can be set to null, but you can't have an array with some elements
154 The result of the previous two inserts looks like this:
156 SELECT * FROM sal_emp;
157 name | pay_by_quarter | schedule
158 -------+---------------------------+-------------------------------------------
159 Bill | {10000,10000,10000,10000} | {{meeting,lunch},{training,presentation}}
160 Carol | {20000,25000,25000,25000} | {{breakfast,consulting},{meeting,lunch}}
166 The <literal>ARRAY</> constructor syntax may also be used:
170 ARRAY[10000, 10000, 10000, 10000],
171 ARRAY[['meeting', 'lunch'], ['training', 'presentation']]);
175 ARRAY[20000, 25000, 25000, 25000],
176 ARRAY[['breakfast', 'consulting'], ['meeting', 'lunch']]);
178 Notice that the array elements are ordinary SQL constants or
179 expressions; for instance, string literals are single quoted, instead of
180 double quoted as they would be in an array literal. The <literal>ARRAY</>
181 constructor syntax is discussed in more detail in
182 <xref linkend="sql-syntax-array-constructors">.
187 <title>Accessing Arrays</title>
190 Now, we can run some queries on the table.
191 First, we show how to access a single element of an array at a time.
192 This query retrieves the names of the employees whose pay changed in
196 SELECT name FROM sal_emp WHERE pay_by_quarter[1] <> pay_by_quarter[2];
204 The array subscript numbers are written within square brackets.
205 By default <productname>PostgreSQL</productname> uses the
206 one-based numbering convention for arrays, that is,
207 an array of <replaceable>n</> elements starts with <literal>array[1]</literal> and
208 ends with <literal>array[<replaceable>n</>]</literal>.
212 This query retrieves the third quarter pay of all employees:
215 SELECT pay_by_quarter[3] FROM sal_emp;
226 We can also access arbitrary rectangular slices of an array, or
227 subarrays. An array slice is denoted by writing
228 <literal><replaceable>lower-bound</replaceable>:<replaceable>upper-bound</replaceable></literal>
229 for one or more array dimensions. For example, this query retrieves the first
230 item on Bill's schedule for the first two days of the week:
233 SELECT schedule[1:2][1:1] FROM sal_emp WHERE name = 'Bill';
236 ------------------------
237 {{meeting},{training}}
241 We could also have written
244 SELECT schedule[1:2][1] FROM sal_emp WHERE name = 'Bill';
247 with the same result. An array subscripting operation is always taken to
248 represent an array slice if any of the subscripts are written in the form
249 <literal><replaceable>lower</replaceable>:<replaceable>upper</replaceable></literal>.
250 A lower bound of 1 is assumed for any subscript where only one value
251 is specified, as in this example:
253 SELECT schedule[1:2][2] FROM sal_emp WHERE name = 'Bill';
256 -------------------------------------------
257 {{meeting,lunch},{training,presentation}}
263 The current dimensions of any array value can be retrieved with the
264 <function>array_dims</function> function:
267 SELECT array_dims(schedule) FROM sal_emp WHERE name = 'Carol';
275 <function>array_dims</function> produces a <type>text</type> result,
276 which is convenient for people to read but perhaps not so convenient
277 for programs. Dimensions can also be retrieved with
278 <function>array_upper</function> and <function>array_lower</function>,
279 which return the upper and lower bound of a
280 specified array dimension, respectively.
283 SELECT array_upper(schedule, 1) FROM sal_emp WHERE name = 'Carol';
294 <title>Modifying Arrays</title>
297 An array value can be replaced completely:
300 UPDATE sal_emp SET pay_by_quarter = '{25000,25000,27000,27000}'
301 WHERE name = 'Carol';
304 or using the <literal>ARRAY</literal> expression syntax:
307 UPDATE sal_emp SET pay_by_quarter = ARRAY[25000,25000,27000,27000]
308 WHERE name = 'Carol';
311 An array may also be updated at a single element:
314 UPDATE sal_emp SET pay_by_quarter[4] = 15000
318 or updated in a slice:
321 UPDATE sal_emp SET pay_by_quarter[1:2] = '{27000,27000}'
322 WHERE name = 'Carol';
328 A stored array value can be enlarged by assigning to an element adjacent to
329 those already present, or by assigning to a slice that is adjacent
330 to or overlaps the data already present. For example, if array
331 <literal>myarray</> currently has 4 elements, it will have five
332 elements after an update that assigns to <literal>myarray[5]</>.
333 Currently, enlargement in this fashion is only allowed for one-dimensional
334 arrays, not multidimensional arrays.
338 Array slice assignment allows creation of arrays that do not use one-based
339 subscripts. For example one might assign to <literal>myarray[-2:7]</> to
340 create an array with subscript values running from -2 to 7.
344 New array values can also be constructed by using the concatenation operator,
345 <literal>||</literal>.
347 SELECT ARRAY[1,2] || ARRAY[3,4];
353 SELECT ARRAY[5,6] || ARRAY[[1,2],[3,4]];
355 ---------------------
362 The concatenation operator allows a single element to be pushed on to the
363 beginning or end of a one-dimensional array. It also accepts two
364 <replaceable>N</>-dimensional arrays, or an <replaceable>N</>-dimensional
365 and an <replaceable>N+1</>-dimensional array.
369 When a single element is pushed on to the beginning of a one-dimensional
370 array, the result is an array with a lower bound subscript equal to
371 the right-hand operand's lower bound subscript, minus one. When a single
372 element is pushed on to the end of a one-dimensional array, the result is
373 an array retaining the lower bound of the left-hand operand. For example:
375 SELECT array_dims(1 || ARRAY[2,3]);
381 SELECT array_dims(ARRAY[1,2] || 3);
390 When two arrays with an equal number of dimensions are concatenated, the
391 result retains the lower bound subscript of the left-hand operand's outer
392 dimension. The result is an array comprising every element of the left-hand
393 operand followed by every element of the right-hand operand. For example:
395 SELECT array_dims(ARRAY[1,2] || ARRAY[3,4,5]);
401 SELECT array_dims(ARRAY[[1,2],[3,4]] || ARRAY[[5,6],[7,8],[9,0]]);
410 When an <replaceable>N</>-dimensional array is pushed on to the beginning
411 or end of an <replaceable>N+1</>-dimensional array, the result is
412 analogous to the element-array case above. Each <replaceable>N</>-dimensional
413 sub-array is essentially an element of the <replaceable>N+1</>-dimensional
414 array's outer dimension. For example:
416 SELECT array_dims(ARRAY[1,2] || ARRAY[[3,4],[5,6]]);
425 An array can also be constructed by using the functions
426 <function>array_prepend</function>, <function>array_append</function>,
427 or <function>array_cat</function>. The first two only support one-dimensional
428 arrays, but <function>array_cat</function> supports multidimensional arrays.
430 Note that the concatenation operator discussed above is preferred over
431 direct use of these functions. In fact, the functions are primarily for use
432 in implementing the concatenation operator. However, they may be directly
433 useful in the creation of user-defined aggregates. Some examples:
436 SELECT array_prepend(1, ARRAY[2,3]);
442 SELECT array_append(ARRAY[1,2], 3);
448 SELECT array_cat(ARRAY[1,2], ARRAY[3,4]);
454 SELECT array_cat(ARRAY[[1,2],[3,4]], ARRAY[5,6]);
456 ---------------------
460 SELECT array_cat(ARRAY[5,6], ARRAY[[1,2],[3,4]]);
462 ---------------------
469 <title>Searching in Arrays</title>
472 To search for a value in an array, you must check each value of the
473 array. This can be done by hand, if you know the size of the array.
477 SELECT * FROM sal_emp WHERE pay_by_quarter[1] = 10000 OR
478 pay_by_quarter[2] = 10000 OR
479 pay_by_quarter[3] = 10000 OR
480 pay_by_quarter[4] = 10000;
483 However, this quickly becomes tedious for large arrays, and is not
484 helpful if the size of the array is uncertain. An alternative method is
485 described in <xref linkend="functions-comparisons">. The above
486 query could be replaced by:
489 SELECT * FROM sal_emp WHERE 10000 = ANY (pay_by_quarter);
492 In addition, you could find rows where the array had all values
496 SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter);
503 Arrays are not sets; searching for specific array elements
504 may be a sign of database misdesign. Consider
505 using a separate table with a row for each item that would be an
506 array element. This will be easier to search, and is likely to
507 scale up better to large numbers of elements.
513 <title>Array Input and Output Syntax</title>
516 The external text representation of an array value consists of items that
517 are interpreted according to the I/O conversion rules for the array's
518 element type, plus decoration that indicates the array structure.
519 The decoration consists of curly braces (<literal>{</> and <literal>}</>)
520 around the array value plus delimiter characters between adjacent items.
521 The delimiter character is usually a comma (<literal>,</>) but can be
522 something else: it is determined by the <literal>typdelim</> setting
523 for the array's element type. (Among the standard data types provided
524 in the <productname>PostgreSQL</productname> distribution, type
525 <literal>box</> uses a semicolon (<literal>;</>) but all the others
526 use comma.) In a multidimensional array, each dimension (row, plane,
527 cube, etc.) gets its own level of curly braces, and delimiters
528 must be written between adjacent curly-braced entities of the same level.
532 The array output routine will put double quotes around element values
533 if they are empty strings or contain curly braces, delimiter characters,
534 double quotes, backslashes, or white space. Double quotes and backslashes
535 embedded in element values will be backslash-escaped. For numeric
536 data types it is safe to assume that double quotes will never appear, but
537 for textual data types one should be prepared to cope with either presence
538 or absence of quotes. (This is a change in behavior from pre-7.2
539 <productname>PostgreSQL</productname> releases.)
543 By default, the lower bound index value of an array's dimensions is
544 set to one. If any of an array's dimensions has a lower bound index not
545 equal to one, an additional decoration that indicates the actual
546 array dimensions will precede the array structure decoration.
547 The decoration consists of square braces (<literal>[</> and <literal>]</>)
548 around each array dimension's lower and upper bound indicies, plus
549 a colon (<literal>:</>) delimiter character inbetween. Delimiting the
550 array dimension decoration from the array structure decoration is a
551 single assignment operator (<literal>=</>). For example:
553 SELECT 1 || ARRAY[2,3] AS array;
560 SELECT ARRAY[1,2] || ARRAY[[3,4]] AS array;
563 --------------------------
564 [0:1][1:2]={{1,2},{3,4}}
570 In a similar fashion, an array with non-default indicies may be specified
571 using the same literal syntax. For example:
573 SELECT f1[1][-2][3] AS e1, f1[1][-1][5] AS e2
574 FROM (SELECT '[1:1][-2:-1][3:5]={{{1,2,3},{4,5,6}}}'::int[] AS f1) AS ss;
584 As shown previously, when writing an array value you may write double
585 quotes around any individual array element. You <emphasis>must</> do so
586 if the element value would otherwise confuse the array-value parser.
587 For example, elements containing curly braces, commas (or whatever the
588 delimiter character is), double quotes, backslashes, or leading or trailing
589 whitespace must be double-quoted. To put a double quote or backslash in a
590 quoted array element value, precede it with a backslash. Alternatively, you
591 can use backslash-escaping to protect all data characters that would
592 otherwise be taken as array syntax.
596 You may write whitespace before a left brace or after a right
597 brace. You may also write whitespace before or after any individual item
598 string. In all of these cases the whitespace will be ignored. However,
599 whitespace within double quoted elements, or surrounded on both sides by
600 non-whitespace characters of an element, are not ignored.
605 Remember that what you write in an SQL command will first be interpreted
606 as a string literal, and then as an array. This doubles the number of
607 backslashes you need. For example, to insert a <type>text</> array
608 value containing a backslash and a double quote, you'd need to write
610 INSERT ... VALUES ('{"\\\\","\\""}');
612 The string-literal processor removes one level of backslashes, so that
613 what arrives at the array-value parser looks like <literal>{"\\","\""}</>.
614 In turn, the strings fed to the <type>text</> data type's input routine
615 become <literal>\</> and <literal>"</> respectively. (If we were working
616 with a data type whose input routine also treated backslashes specially,
617 <type>bytea</> for example, we might need as many as eight backslashes
618 in the command to get one backslash into the stored array element.)
624 The <literal>ARRAY</> constructor syntax (see
625 <xref linkend="sql-syntax-array-constructors">) is often easier to work
626 with than the array-literal syntax when writing array values in SQL
627 commands. In <literal>ARRAY</>, individual element values are written the
628 same way they would be written when not members of an array.