1 <!-- $Header: /cvsroot/pgsql/doc/src/sgml/array.sgml,v 1.29 2003/08/09 22:50:21 tgl Exp $ -->
7 <primary>arrays</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 Now we can show some <command>INSERT</command> statements. To write an array
81 value as a literal constant, we enclose the element values within curly
82 braces and separate them by commas. (If you know C, this is not unlike the
83 C syntax for initializing structures.) We may put double quotes around any
84 element value, and must do so if it contains commas or curly braces.
85 (More details appear below.)
90 '{10000, 10000, 10000, 10000}',
91 '{{"meeting", "lunch"}, {}}');
95 '{20000, 25000, 25000, 25000}',
96 '{{"talk", "consult"}, {"meeting"}}');
101 A limitation of the present array implementation is that individual
102 elements of an array cannot be SQL null values. The entire array can be set
103 to null, but you can't have an array with some elements null and some
107 This can lead to surprising results. For example, the result of the
108 previous two inserts looks like this:
110 SELECT * FROM sal_emp;
111 name | pay_by_quarter | schedule
112 -------+---------------------------+--------------------
113 Bill | {10000,10000,10000,10000} | {{meeting},{""}}
114 Carol | {20000,25000,25000,25000} | {{talk},{meeting}}
117 Because the <literal>[2][2]</literal> element of
118 <structfield>schedule</structfield> is missing in each of the
119 <command>INSERT</command> statements, the <literal>[1][2]</literal>
120 element is discarded.
125 Fixing this is on the to-do list.
130 The <literal>ARRAY</literal> expression syntax may also be used:
134 ARRAY[10000, 10000, 10000, 10000],
135 ARRAY[['meeting', 'lunch'], ['','']]);
139 ARRAY[20000, 25000, 25000, 25000],
140 ARRAY[['talk', 'consult'], ['meeting', '']]);
141 SELECT * FROM sal_emp;
142 name | pay_by_quarter | schedule
143 -------+---------------------------+-------------------------------
144 Bill | {10000,10000,10000,10000} | {{meeting,lunch},{"",""}}
145 Carol | {20000,25000,25000,25000} | {{talk,consult},{meeting,""}}
148 Note that with this syntax, multidimensional arrays must have matching
149 extents for each dimension. A mismatch causes an error report, rather than
150 silently discarding values as in the previous case.
155 ARRAY[20000, 25000, 25000, 25000],
156 ARRAY[['talk', 'consult'], ['meeting']]);
157 ERROR: multidimensional arrays must have array expressions with matching dimensions
159 Also notice that the array elements are ordinary SQL constants or
160 expressions; for instance, string literals are single quoted, instead of
161 double quoted as they would be in an array literal. The <literal>ARRAY</>
162 expression syntax is discussed in more detail in <xref
163 linkend="sql-syntax-array-constructors">.
169 <title>Accessing Arrays</title>
172 Now, we can run some queries on the table.
173 First, we show how to access a single element of an array at a time.
174 This query retrieves the names of the employees whose pay changed in
178 SELECT name FROM sal_emp WHERE pay_by_quarter[1] <> pay_by_quarter[2];
186 The array subscript numbers are written within square brackets.
187 By default <productname>PostgreSQL</productname> uses the
188 one-based numbering convention for arrays, that is,
189 an array of <replaceable>n</> elements starts with <literal>array[1]</literal> and
190 ends with <literal>array[<replaceable>n</>]</literal>.
194 This query retrieves the third quarter pay of all employees:
197 SELECT pay_by_quarter[3] FROM sal_emp;
208 We can also access arbitrary rectangular slices of an array, or
209 subarrays. An array slice is denoted by writing
210 <literal><replaceable>lower-bound</replaceable>:<replaceable>upper-bound</replaceable></literal>
211 for one or more array dimensions. For example, this query retrieves the first
212 item on Bill's schedule for the first two days of the week:
215 SELECT schedule[1:2][1:1] FROM sal_emp WHERE name = 'Bill';
223 We could also have written
226 SELECT schedule[1:2][1] FROM sal_emp WHERE name = 'Bill';
229 with the same result. An array subscripting operation is always taken to
230 represent an array slice if any of the subscripts are written in the form
231 <literal><replaceable>lower</replaceable>:<replaceable>upper</replaceable></literal>.
232 A lower bound of 1 is assumed for any subscript where only one value
233 is specified, as in this example:
235 SELECT schedule[1:2][2] FROM sal_emp WHERE name = 'Bill';
237 ---------------------------
238 {{meeting,lunch},{"",""}}
244 The current dimensions of any array value can be retrieved with the
245 <function>array_dims</function> function:
248 SELECT array_dims(schedule) FROM sal_emp WHERE name = 'Carol';
256 <function>array_dims</function> produces a <type>text</type> result,
257 which is convenient for people to read but perhaps not so convenient
258 for programs. Dimensions can also be retrieved with
259 <function>array_upper</function> and <function>array_lower</function>,
260 which return the upper and lower bound of a
261 specified array dimension, respectively.
264 SELECT array_upper(schedule, 1) FROM sal_emp WHERE name = 'Carol';
275 <title>Modifying Arrays</title>
278 An array value can be replaced completely:
281 UPDATE sal_emp SET pay_by_quarter = '{25000,25000,27000,27000}'
282 WHERE name = 'Carol';
285 or using the <literal>ARRAY</literal> expression syntax:
288 UPDATE sal_emp SET pay_by_quarter = ARRAY[25000,25000,27000,27000]
289 WHERE name = 'Carol';
292 An array may also be updated at a single element:
295 UPDATE sal_emp SET pay_by_quarter[4] = 15000
299 or updated in a slice:
302 UPDATE sal_emp SET pay_by_quarter[1:2] = '{27000,27000}'
303 WHERE name = 'Carol';
309 A stored array value can be enlarged by assigning to an element adjacent to
310 those already present, or by assigning to a slice that is adjacent
311 to or overlaps the data already present. For example, if array
312 <literal>myarray</> currently has 4 elements, it will have five
313 elements after an update that assigns to <literal>myarray[5]</>.
314 Currently, enlargement in this fashion is only allowed for one-dimensional
315 arrays, not multidimensional arrays.
319 Array slice assignment allows creation of arrays that do not use one-based
320 subscripts. For example one might assign to <literal>myarray[-2:7]</> to
321 create an array with subscript values running from -2 to 7.
325 New array values can also be constructed by using the concatenation operator,
326 <literal>||</literal>.
328 SELECT ARRAY[1,2] || ARRAY[3,4];
334 SELECT ARRAY[5,6] || ARRAY[[1,2],[3,4]];
336 ---------------------
341 The concatenation operator allows a single element to be pushed on to the
342 beginning or end of a one-dimensional array. It also accepts two
343 <replaceable>N</>-dimensional arrays, or an <replaceable>N</>-dimensional
344 and an <replaceable>N+1</>-dimensional array. In the former case, the two
345 <replaceable>N</>-dimension arrays become outer elements of an
346 <replaceable>N+1</>-dimensional array. In the latter, the
347 <replaceable>N</>-dimensional array is added as either the first or last
348 outer element of the <replaceable>N+1</>-dimensional array.
350 When extending an array by concatenation, the subscripts of its existing
351 elements are preserved. For example, when pushing
352 onto the beginning of an array with one-based subscripts, the resulting
353 array has zero-based subscripts:
356 SELECT array_dims(1 || ARRAY[2,3]);
365 An array can also be constructed by using the functions
366 <function>array_prepend</function>, <function>array_append</function>,
367 or <function>array_cat</function>. The first two only support one-dimensional
368 arrays, but <function>array_cat</function> supports multidimensional arrays.
370 Note that the concatenation operator discussed above is preferred over
371 direct use of these functions. In fact, the functions are primarily for use
372 in implementing the concatenation operator. However, they may be directly
373 useful in the creation of user-defined aggregates. Some examples:
376 SELECT array_prepend(1, ARRAY[2,3]);
382 SELECT array_append(ARRAY[1,2], 3);
388 SELECT array_cat(ARRAY[1,2], ARRAY[3,4]);
394 SELECT array_cat(ARRAY[[1,2],[3,4]], ARRAY[5,6]);
396 ---------------------
400 SELECT array_cat(ARRAY[5,6], ARRAY[[1,2],[3,4]]);
402 ---------------------
409 <title>Searching in Arrays</title>
412 To search for a value in an array, you must check each value of the
413 array. This can be done by hand, if you know the size of the array.
417 SELECT * FROM sal_emp WHERE pay_by_quarter[1] = 10000 OR
418 pay_by_quarter[2] = 10000 OR
419 pay_by_quarter[3] = 10000 OR
420 pay_by_quarter[4] = 10000;
423 However, this quickly becomes tedious for large arrays, and is not
424 helpful if the size of the array is uncertain. An alternative method is
425 described in <xref linkend="functions-comparisons">. The above
426 query could be replaced by:
429 SELECT * FROM sal_emp WHERE 10000 = ANY (pay_by_quarter);
432 In addition, you could find rows where the array had all values
436 SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter);
443 Arrays are not sets; searching for specific array elements
444 may be a sign of database misdesign. Consider
445 using a separate table with a row for each item that would be an
446 array element. This will be easier to search, and is likely to
447 scale up better to large numbers of elements.
453 <title>Array Input and Output Syntax</title>
456 The external text representation of an array value consists of items that
457 are interpreted according to the I/O conversion rules for the array's
458 element type, plus decoration that indicates the array structure.
459 The decoration consists of curly braces (<literal>{</> and <literal>}</>)
460 around the array value plus delimiter characters between adjacent items.
461 The delimiter character is usually a comma (<literal>,</>) but can be
462 something else: it is determined by the <literal>typdelim</> setting
463 for the array's element type. (Among the standard data types provided
464 in the <productname>PostgreSQL</productname> distribution, type
465 <literal>box</> uses a semicolon (<literal>;</>) but all the others
466 use comma.) In a multidimensional array, each dimension (row, plane,
467 cube, etc.) gets its own level of curly braces, and delimiters
468 must be written between adjacent curly-braced entities of the same level.
469 You may write whitespace before a left brace, after a right
470 brace, or before any individual item string. Whitespace after an item
471 is not ignored, however: after skipping leading whitespace, everything
472 up to the next right brace or delimiter is taken as the item value.
476 As shown previously, when writing an array value you may write double
477 quotes around any individual array
478 element. You <emphasis>must</> do so if the element value would otherwise
479 confuse the array-value parser. For example, elements containing curly
480 braces, commas (or whatever the delimiter character is), double quotes,
481 backslashes, or leading white space must be double-quoted. To put a double
482 quote or backslash in a quoted array element value, precede it with a
484 Alternatively, you can use backslash-escaping to protect all data characters
485 that would otherwise be taken as array syntax or ignorable white space.
489 The array output routine will put double quotes around element values
490 if they are empty strings or contain curly braces, delimiter characters,
491 double quotes, backslashes, or white space. Double quotes and backslashes
492 embedded in element values will be backslash-escaped. For numeric
493 data types it is safe to assume that double quotes will never appear, but
494 for textual data types one should be prepared to cope with either presence
495 or absence of quotes. (This is a change in behavior from pre-7.2
496 <productname>PostgreSQL</productname> releases.)
501 Remember that what you write in an SQL command will first be interpreted
502 as a string literal, and then as an array. This doubles the number of
503 backslashes you need. For example, to insert a <type>text</> array
504 value containing a backslash and a double quote, you'd need to write
506 INSERT ... VALUES ('{"\\\\","\\""}');
508 The string-literal processor removes one level of backslashes, so that
509 what arrives at the array-value parser looks like <literal>{"\\","\""}</>.
510 In turn, the strings fed to the <type>text</> data type's input routine
511 become <literal>\</> and <literal>"</> respectively. (If we were working
512 with a data type whose input routine also treated backslashes specially,
513 <type>bytea</> for example, we might need as many as eight backslashes
514 in the command to get one backslash into the stored array element.)
520 The <literal>ARRAY</> constructor syntax is often easier to work with
521 than the array-literal syntax when writing array values in SQL commands.
522 In <literal>ARRAY</>, individual element values are written the same way
523 they would be written when not members of an array.