1 <!-- $Header: /cvsroot/pgsql/doc/src/sgml/array.sgml,v 1.33 2003/11/04 09:55:38 petere 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 type or user-defined type can be created.
17 <title>Declaration of Array Types</title>
20 To illustrate the use of array types, we create this table:
22 CREATE TABLE sal_emp (
24 pay_by_quarter integer[],
28 As shown, an array data type is named by appending square brackets
29 (<literal>[]</>) to the data type name of the array elements. The
30 above command will create a table named
31 <structname>sal_emp</structname> with a column of type
32 <type>text</type> (<structfield>name</structfield>), a
33 one-dimensional array of type <type>integer</type>
34 (<structfield>pay_by_quarter</structfield>), which represents the
35 employee's salary by quarter, and a two-dimensional array of
36 <type>text</type> (<structfield>schedule</structfield>), which
37 represents the employee's weekly schedule.
41 The syntax for <command>CREATE TABLE</command> allows the exact size of
42 arrays to be specified, for example:
45 CREATE TABLE tictactoe (
50 However, the current implementation does not enforce the array size
51 limits --- the behavior is the same as for arrays of unspecified
56 Actually, the current implementation does not enforce the declared
57 number of dimensions either. Arrays of a particular element type are
58 all considered to be of the same type, regardless of size or number
59 of dimensions. So, declaring number of dimensions or sizes in
60 <command>CREATE TABLE</command> is simply documentation, it does not
61 affect runtime behavior.
65 An alternative, SQL99-standard syntax may be used for one-dimensional arrays.
66 <structfield>pay_by_quarter</structfield> could have been defined as:
68 pay_by_quarter integer ARRAY[4],
70 This syntax requires an integer constant to denote the array size.
71 As before, however, <productname>PostgreSQL</> does not enforce the
77 <title>Array Value Input</title>
80 <primary>array</primary>
81 <secondary>constant</secondary>
85 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 '{ <replaceable>val1</replaceable> <replaceable>delim</replaceable> <replaceable>val2</replaceable> <replaceable>delim</replaceable> ... }'
95 where <replaceable>delim</replaceable> is the delimiter character
96 for the type, as recorded in its <literal>pg_type</literal> entry.
97 (For all built-in types, this is the comma character
98 <quote><literal>,</literal></>.) Each
99 <replaceable>val</replaceable> is either a constant of the array
100 element type, or a subarray. An example of an array constant is
102 '{{1,2,3},{4,5,6},{7,8,9}}'
104 This constant is a two-dimensional, 3-by-3 array consisting of
105 three subarrays of integers.
109 (These kinds of array constants are actually only a special case of
110 the generic type constants discussed in <xref
111 linkend="sql-syntax-constants-generic">. The constant is initially
112 treated as a string and passed to the array input conversion
113 routine. An explicit type specification might be necessary.)
117 Now we can show some <command>INSERT</command> statements.
122 '{10000, 10000, 10000, 10000}',
123 '{{"meeting", "lunch"}, {}}');
127 '{20000, 25000, 25000, 25000}',
128 '{{"talk", "consult"}, {"meeting"}}');
133 A limitation of the present array implementation is that individual
134 elements of an array cannot be SQL null values. The entire array
135 can be set to null, but you can't have an array with some elements
140 This can lead to surprising results. For example, the result of the
141 previous two inserts looks like this:
143 SELECT * FROM sal_emp;
144 name | pay_by_quarter | schedule
145 -------+---------------------------+--------------------
146 Bill | {10000,10000,10000,10000} | {{meeting},{""}}
147 Carol | {20000,25000,25000,25000} | {{talk},{meeting}}
150 Because the <literal>[2][2]</literal> element of
151 <structfield>schedule</structfield> is missing in each of the
152 <command>INSERT</command> statements, the <literal>[1][2]</literal>
153 element is discarded.
158 Fixing this is on the to-do list.
163 The <literal>ARRAY</literal> expression syntax may also be used:
167 ARRAY[10000, 10000, 10000, 10000],
168 ARRAY[['meeting', 'lunch'], ['','']]);
172 ARRAY[20000, 25000, 25000, 25000],
173 ARRAY[['talk', 'consult'], ['meeting', '']]);
174 SELECT * FROM sal_emp;
175 name | pay_by_quarter | schedule
176 -------+---------------------------+-------------------------------
177 Bill | {10000,10000,10000,10000} | {{meeting,lunch},{"",""}}
178 Carol | {20000,25000,25000,25000} | {{talk,consult},{meeting,""}}
181 Note that with this syntax, multidimensional arrays must have matching
182 extents for each dimension. A mismatch causes an error report, rather than
183 silently discarding values as in the previous case.
188 ARRAY[20000, 25000, 25000, 25000],
189 ARRAY[['talk', 'consult'], ['meeting']]);
190 ERROR: multidimensional arrays must have array expressions with matching dimensions
192 Also 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 expression syntax is discussed in more detail in <xref
196 linkend="sql-syntax-array-constructors">.
201 <title>Accessing Arrays</title>
204 Now, we can run some queries on the table.
205 First, we show how to access a single element of an array at a time.
206 This query retrieves the names of the employees whose pay changed in
210 SELECT name FROM sal_emp WHERE pay_by_quarter[1] <> pay_by_quarter[2];
218 The array subscript numbers are written within square brackets.
219 By default <productname>PostgreSQL</productname> uses the
220 one-based numbering convention for arrays, that is,
221 an array of <replaceable>n</> elements starts with <literal>array[1]</literal> and
222 ends with <literal>array[<replaceable>n</>]</literal>.
226 This query retrieves the third quarter pay of all employees:
229 SELECT pay_by_quarter[3] FROM sal_emp;
240 We can also access arbitrary rectangular slices of an array, or
241 subarrays. An array slice is denoted by writing
242 <literal><replaceable>lower-bound</replaceable>:<replaceable>upper-bound</replaceable></literal>
243 for one or more array dimensions. For example, this query retrieves the first
244 item on Bill's schedule for the first two days of the week:
247 SELECT schedule[1:2][1:1] FROM sal_emp WHERE name = 'Bill';
255 We could also have written
258 SELECT schedule[1:2][1] FROM sal_emp WHERE name = 'Bill';
261 with the same result. An array subscripting operation is always taken to
262 represent an array slice if any of the subscripts are written in the form
263 <literal><replaceable>lower</replaceable>:<replaceable>upper</replaceable></literal>.
264 A lower bound of 1 is assumed for any subscript where only one value
265 is specified, as in this example:
267 SELECT schedule[1:2][2] FROM sal_emp WHERE name = 'Bill';
269 ---------------------------
270 {{meeting,lunch},{"",""}}
276 The current dimensions of any array value can be retrieved with the
277 <function>array_dims</function> function:
280 SELECT array_dims(schedule) FROM sal_emp WHERE name = 'Carol';
288 <function>array_dims</function> produces a <type>text</type> result,
289 which is convenient for people to read but perhaps not so convenient
290 for programs. Dimensions can also be retrieved with
291 <function>array_upper</function> and <function>array_lower</function>,
292 which return the upper and lower bound of a
293 specified array dimension, respectively.
296 SELECT array_upper(schedule, 1) FROM sal_emp WHERE name = 'Carol';
307 <title>Modifying Arrays</title>
310 An array value can be replaced completely:
313 UPDATE sal_emp SET pay_by_quarter = '{25000,25000,27000,27000}'
314 WHERE name = 'Carol';
317 or using the <literal>ARRAY</literal> expression syntax:
320 UPDATE sal_emp SET pay_by_quarter = ARRAY[25000,25000,27000,27000]
321 WHERE name = 'Carol';
324 An array may also be updated at a single element:
327 UPDATE sal_emp SET pay_by_quarter[4] = 15000
331 or updated in a slice:
334 UPDATE sal_emp SET pay_by_quarter[1:2] = '{27000,27000}'
335 WHERE name = 'Carol';
341 A stored array value can be enlarged by assigning to an element adjacent to
342 those already present, or by assigning to a slice that is adjacent
343 to or overlaps the data already present. For example, if array
344 <literal>myarray</> currently has 4 elements, it will have five
345 elements after an update that assigns to <literal>myarray[5]</>.
346 Currently, enlargement in this fashion is only allowed for one-dimensional
347 arrays, not multidimensional arrays.
351 Array slice assignment allows creation of arrays that do not use one-based
352 subscripts. For example one might assign to <literal>myarray[-2:7]</> to
353 create an array with subscript values running from -2 to 7.
357 New array values can also be constructed by using the concatenation operator,
358 <literal>||</literal>.
360 SELECT ARRAY[1,2] || ARRAY[3,4];
366 SELECT ARRAY[5,6] || ARRAY[[1,2],[3,4]];
368 ---------------------
375 The concatenation operator allows a single element to be pushed on to the
376 beginning or end of a one-dimensional array. It also accepts two
377 <replaceable>N</>-dimensional arrays, or an <replaceable>N</>-dimensional
378 and an <replaceable>N+1</>-dimensional array.
382 When a single element is pushed on to the beginning of a one-dimensional
383 array, the result is an array with a lower bound subscript equal to
384 the right-hand operand's lower bound subscript, minus one. When a single
385 element is pushed on to the end of a one-dimensional array, the result is
386 an array retaining the lower bound of the left-hand operand. For example:
388 SELECT array_dims(1 || ARRAY[2,3]);
394 SELECT array_dims(ARRAY[1,2] || 3);
403 When two arrays with an equal number of dimensions are concatenated, the
404 result retains the lower bound subscript of the left-hand operand's outer
405 dimension. The result is an array comprising every element of the left-hand
406 operand followed by every element of the right-hand operand. For example:
408 SELECT array_dims(ARRAY[1,2] || ARRAY[3,4,5]);
414 SELECT array_dims(ARRAY[[1,2],[3,4]] || ARRAY[[5,6],[7,8],[9,0]]);
423 When an <replaceable>N</>-dimensional array is pushed on to the beginning
424 or end of an <replaceable>N+1</>-dimensional array, the result is
425 analogous to the element-array case above. Each <replaceable>N</>-dimensional
426 sub-array is essentially an element of the <replaceable>N+1</>-dimensional
427 array's outer dimension. For example:
429 SELECT array_dims(ARRAY[1,2] || ARRAY[[3,4],[5,6]]);
438 An array can also be constructed by using the functions
439 <function>array_prepend</function>, <function>array_append</function>,
440 or <function>array_cat</function>. The first two only support one-dimensional
441 arrays, but <function>array_cat</function> supports multidimensional arrays.
443 Note that the concatenation operator discussed above is preferred over
444 direct use of these functions. In fact, the functions are primarily for use
445 in implementing the concatenation operator. However, they may be directly
446 useful in the creation of user-defined aggregates. Some examples:
449 SELECT array_prepend(1, ARRAY[2,3]);
455 SELECT array_append(ARRAY[1,2], 3);
461 SELECT array_cat(ARRAY[1,2], ARRAY[3,4]);
467 SELECT array_cat(ARRAY[[1,2],[3,4]], ARRAY[5,6]);
469 ---------------------
473 SELECT array_cat(ARRAY[5,6], ARRAY[[1,2],[3,4]]);
475 ---------------------
482 <title>Searching in Arrays</title>
485 To search for a value in an array, you must check each value of the
486 array. This can be done by hand, if you know the size of the array.
490 SELECT * FROM sal_emp WHERE pay_by_quarter[1] = 10000 OR
491 pay_by_quarter[2] = 10000 OR
492 pay_by_quarter[3] = 10000 OR
493 pay_by_quarter[4] = 10000;
496 However, this quickly becomes tedious for large arrays, and is not
497 helpful if the size of the array is uncertain. An alternative method is
498 described in <xref linkend="functions-comparisons">. The above
499 query could be replaced by:
502 SELECT * FROM sal_emp WHERE 10000 = ANY (pay_by_quarter);
505 In addition, you could find rows where the array had all values
509 SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter);
516 Arrays are not sets; searching for specific array elements
517 may be a sign of database misdesign. Consider
518 using a separate table with a row for each item that would be an
519 array element. This will be easier to search, and is likely to
520 scale up better to large numbers of elements.
526 <title>Array Input and Output Syntax</title>
529 The external text representation of an array value consists of items that
530 are interpreted according to the I/O conversion rules for the array's
531 element type, plus decoration that indicates the array structure.
532 The decoration consists of curly braces (<literal>{</> and <literal>}</>)
533 around the array value plus delimiter characters between adjacent items.
534 The delimiter character is usually a comma (<literal>,</>) but can be
535 something else: it is determined by the <literal>typdelim</> setting
536 for the array's element type. (Among the standard data types provided
537 in the <productname>PostgreSQL</productname> distribution, type
538 <literal>box</> uses a semicolon (<literal>;</>) but all the others
539 use comma.) In a multidimensional array, each dimension (row, plane,
540 cube, etc.) gets its own level of curly braces, and delimiters
541 must be written between adjacent curly-braced entities of the same level.
542 You may write whitespace before a left brace, after a right
543 brace, or before any individual item string. Whitespace after an item
544 is not ignored, however: after skipping leading whitespace, everything
545 up to the next right brace or delimiter is taken as the item value.
549 As shown previously, when writing an array value you may write double
550 quotes around any individual array
551 element. You <emphasis>must</> do so if the element value would otherwise
552 confuse the array-value parser. For example, elements containing curly
553 braces, commas (or whatever the delimiter character is), double quotes,
554 backslashes, or leading white space must be double-quoted. To put a double
555 quote or backslash in a quoted array element value, precede it with a
557 Alternatively, you can use backslash-escaping to protect all data characters
558 that would otherwise be taken as array syntax or ignorable white space.
562 The array output routine will put double quotes around element values
563 if they are empty strings or contain curly braces, delimiter characters,
564 double quotes, backslashes, or white space. Double quotes and backslashes
565 embedded in element values will be backslash-escaped. For numeric
566 data types it is safe to assume that double quotes will never appear, but
567 for textual data types one should be prepared to cope with either presence
568 or absence of quotes. (This is a change in behavior from pre-7.2
569 <productname>PostgreSQL</productname> releases.)
574 Remember that what you write in an SQL command will first be interpreted
575 as a string literal, and then as an array. This doubles the number of
576 backslashes you need. For example, to insert a <type>text</> array
577 value containing a backslash and a double quote, you'd need to write
579 INSERT ... VALUES ('{"\\\\","\\""}');
581 The string-literal processor removes one level of backslashes, so that
582 what arrives at the array-value parser looks like <literal>{"\\","\""}</>.
583 In turn, the strings fed to the <type>text</> data type's input routine
584 become <literal>\</> and <literal>"</> respectively. (If we were working
585 with a data type whose input routine also treated backslashes specially,
586 <type>bytea</> for example, we might need as many as eight backslashes
587 in the command to get one backslash into the stored array element.)
593 The <literal>ARRAY</> constructor syntax is often easier to work with
594 than the array-literal syntax when writing array values in SQL commands.
595 In <literal>ARRAY</>, individual element values are written the same way
596 they would be written when not members of an array.