1 <!-- $Header: /cvsroot/pgsql/doc/src/sgml/array.sgml,v 1.26 2003/06/24 23:14:42 momjian 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.
42 <title>Array Value Input</title>
45 Now we can show some <command>INSERT</command> statements. To write an array
46 value, we enclose the element values within curly braces and separate them
47 by commas. If you know C, this is not unlike the syntax for
48 initializing structures. (More details appear below.)
53 '{10000, 10000, 10000, 10000}',
54 '{{"meeting", "lunch"}, {}}');
58 '{20000, 25000, 25000, 25000}',
59 '{{"talk", "consult"}, {"meeting"}}');
64 A limitation of the present array implementation is that individual
65 elements of an array cannot be SQL null values. The entire array can be set
66 to null, but you can't have an array with some elements null and some
70 This can lead to surprising results. For example, the result of the
71 previous two inserts looks like this:
73 SELECT * FROM sal_emp;
74 name | pay_by_quarter | schedule
75 -------+---------------------------+--------------------
76 Bill | {10000,10000,10000,10000} | {{meeting},{""}}
77 Carol | {20000,25000,25000,25000} | {{talk},{meeting}}
80 Because the <literal>[2][2]</literal> element of
81 <structfield>schedule</structfield> is missing in each of the
82 <command>INSERT</command> statements, the <literal>[1][2]</literal>
88 Fixing this is on the to-do list.
93 The <command>ARRAY</command> expression syntax may also be used:
97 ARRAY[10000, 10000, 10000, 10000],
98 ARRAY[['meeting', 'lunch'], ['','']]);
102 ARRAY[20000, 25000, 25000, 25000],
103 ARRAY[['talk', 'consult'], ['meeting', '']]);
104 SELECT * FROM sal_emp;
105 name | pay_by_quarter | schedule
106 -------+---------------------------+-------------------------------
107 Bill | {10000,10000,10000,10000} | {{meeting,lunch},{"",""}}
108 Carol | {20000,25000,25000,25000} | {{talk,consult},{meeting,""}}
111 Note that with this syntax, multidimensional arrays must have matching
112 extents for each dimension. This eliminates the missing-array-elements
113 problem above. For example:
117 ARRAY[20000, 25000, 25000, 25000],
118 ARRAY[['talk', 'consult'], ['meeting']]);
119 ERROR: Multidimensional arrays must have array expressions with matching dimensions
121 Also notice that string literals are single quoted instead of double quoted.
126 The examples in the rest of this section are based on the
127 <command>ARRAY</command> expression syntax <command>INSERT</command>s.
134 <title>Array Value References</title>
137 Now, we can run some queries on the table.
138 First, we show how to access a single element of an array at a time.
139 This query retrieves the names of the employees whose pay changed in
143 SELECT name FROM sal_emp WHERE pay_by_quarter[1] <> pay_by_quarter[2];
151 The array subscript numbers are written within square brackets.
152 By default <productname>PostgreSQL</productname> uses the
153 one-based numbering convention for arrays, that is,
154 an array of <replaceable>n</> elements starts with <literal>array[1]</literal> and
155 ends with <literal>array[<replaceable>n</>]</literal>.
159 This query retrieves the third quarter pay of all employees:
162 SELECT pay_by_quarter[3] FROM sal_emp;
173 We can also access arbitrary rectangular slices of an array, or
174 subarrays. An array slice is denoted by writing
175 <literal><replaceable>lower-bound</replaceable>:<replaceable>upper-bound</replaceable></literal>
176 for one or more array dimensions. For example, this query retrieves the first
177 item on Bill's schedule for the first two days of the week:
180 SELECT schedule[1:2][1:1] FROM sal_emp WHERE name = 'Bill';
188 We could also have written
191 SELECT schedule[1:2][1] FROM sal_emp WHERE name = 'Bill';
194 with the same result. An array subscripting operation is always taken to
195 represent an array slice if any of the subscripts are written in the form
196 <literal><replaceable>lower</replaceable>:<replaceable>upper</replaceable></literal>.
197 A lower bound of 1 is assumed for any subscript where only one value
198 is specified; another example follows:
200 SELECT schedule[1:2][2] FROM sal_emp WHERE name = 'Bill';
202 ---------------------------
203 {{meeting,lunch},{"",""}}
209 Additionally, we can also access a single arbitrary array element of
210 a one-dimensional array with the <function>array_subscript</function>
213 SELECT array_subscript(pay_by_quarter, 2) FROM sal_emp WHERE name = 'Bill';
222 An array value can be replaced completely:
225 UPDATE sal_emp SET pay_by_quarter = '{25000,25000,27000,27000}'
226 WHERE name = 'Carol';
229 or using the <command>ARRAY</command> expression syntax:
232 UPDATE sal_emp SET pay_by_quarter = ARRAY[25000,25000,27000,27000]
233 WHERE name = 'Carol';
238 Anywhere you can use the <quote>curly braces</quote> array syntax,
239 you can also use the <command>ARRAY</command> expression syntax. The
240 remainder of this section will illustrate only one or the other, but
245 An array may also be updated at a single element:
248 UPDATE sal_emp SET pay_by_quarter[4] = 15000
252 or updated in a slice:
255 UPDATE sal_emp SET pay_by_quarter[1:2] = '{27000,27000}'
256 WHERE name = 'Carol';
259 A one-dimensional array may also be updated with the
260 <function>array_assign</function> function:
263 UPDATE sal_emp SET pay_by_quarter = array_assign(pay_by_quarter, 4, 15000)
269 An array can be enlarged by assigning to an element adjacent to
270 those already present, or by assigning to a slice that is adjacent
271 to or overlaps the data already present. For example, if an array
272 value currently has 4 elements, it will have five elements after an
273 update that assigns to <literal>array[5]</>. Currently, enlargement in
274 this fashion is only allowed for one-dimensional arrays, not
275 multidimensional arrays.
279 Array slice assignment allows creation of arrays that do not use one-based
280 subscripts. For example one might assign to <literal>array[-2:7]</> to
281 create an array with subscript values running from -2 to 7.
285 An array can also be enlarged by using the concatenation operator,
286 <command>||</command>.
288 SELECT ARRAY[1,2] || ARRAY[3,4];
294 SELECT ARRAY[5,6] || ARRAY[[1,2],[3,4]];
296 ---------------------
301 The concatenation operator allows a single element to be pushed on to the
302 beginning or end of a one-dimensional array. It also allows two
303 <replaceable>N</>-dimensional arrays, or an <replaceable>N</>-dimensional
304 and an <replaceable>N+1</>-dimensional array. In the former case, the two
305 <replaceable>N</>-dimension arrays become outer elements of an
306 <replaceable>N+1</>-dimensional array. In the latter, the
307 <replaceable>N</>-dimensional array is added as either the first or last
308 outer element of the <replaceable>N+1</>-dimensional array.
310 The array is extended in the direction of the push. Hence, by pushing
311 onto the beginning of an array with a one-based subscript, a zero-based
312 subscript array is created:
315 SELECT array_dims(t.f) FROM (SELECT 1 || ARRAY[2,3] AS f) AS t;
324 An array can also be enlarged by using the functions
325 <function>array_prepend</function>, <function>array_append</function>,
326 or <function>array_cat</function>. The first two only support one-dimensional
327 arrays, but <function>array_cat</function> supports multidimensional arrays.
329 Note that the concatenation operator discussed above is preferred over
330 direct use of these functions. In fact, the functions are primarily for use
331 in implementing the concatenation operator. However, they may be directly
332 useful in the creation of user-defined aggregates. Some examples:
335 SELECT array_prepend(1, ARRAY[2,3]);
341 SELECT array_append(ARRAY[1,2], 3);
347 SELECT array_cat(ARRAY[1,2], ARRAY[3,4]);
353 SELECT array_cat(ARRAY[[1,2],[3,4]], ARRAY[5,6]);
355 ---------------------
359 SELECT array_cat(ARRAY[5,6], ARRAY[[1,2],[3,4]]);
361 ---------------------
367 The syntax for <command>CREATE TABLE</command> allows fixed-length
368 arrays to be defined:
371 CREATE TABLE tictactoe (
372 squares integer[3][3]
376 However, the current implementation does not enforce the array size
377 limits --- the behavior is the same as for arrays of unspecified
382 An alternative syntax for one-dimensional arrays may be used.
383 <structfield>pay_by_quarter</structfield> could have been defined as:
385 pay_by_quarter integer ARRAY[4],
387 This syntax may <emphasis>only</emphasis> be used with the integer
388 constant to denote the array size.
392 Actually, the current implementation does not enforce the declared
393 number of dimensions either. Arrays of a particular element type are
394 all considered to be of the same type, regardless of size or number
395 of dimensions. So, declaring number of dimensions or sizes in
396 <command>CREATE TABLE</command> is simply documentation, it does not
397 affect runtime behavior.
401 The current dimensions of any array value can be retrieved with the
402 <function>array_dims</function> function:
405 SELECT array_dims(schedule) FROM sal_emp WHERE name = 'Carol';
413 <function>array_dims</function> produces a <type>text</type> result,
414 which is convenient for people to read but perhaps not so convenient
415 for programs. <function>array_upper</function> and <function>
416 array_lower</function> return the upper/lower bound of the
417 given array dimension, respectively.
422 <title>Searching in Arrays</title>
425 To search for a value in an array, you must check each value of the
426 array. This can be done by hand (if you know the size of the array).
430 SELECT * FROM sal_emp WHERE pay_by_quarter[1] = 10000 OR
431 pay_by_quarter[2] = 10000 OR
432 pay_by_quarter[3] = 10000 OR
433 pay_by_quarter[4] = 10000;
436 However, this quickly becomes tedious for large arrays, and is not
437 helpful if the size of the array is unknown. Although it is not built
438 into <productname>PostgreSQL</productname>,
439 there is an extension available that defines new functions and
440 operators for iterating over array values. Using this, the above
444 SELECT * FROM sal_emp WHERE pay_by_quarter[1:4] *= 10000;
447 To search the entire array (not just specified slices), you could
451 SELECT * FROM sal_emp WHERE pay_by_quarter *= 10000;
454 In addition, you could find rows where the array had all values
455 equal to 10 000 with:
458 SELECT * FROM sal_emp WHERE pay_by_quarter **= 10000;
461 To install this optional module, look in the
462 <filename>contrib/array</filename> directory of the
463 <productname>PostgreSQL</productname> source distribution.
468 Arrays are not sets; using arrays in the manner described in the
469 previous paragraph is often a sign of database misdesign. The
470 array field should generally be split off into a separate table.
471 Tables can obviously be searched easily.
477 <title>Array Input and Output Syntax</title>
480 The external representation of an array value consists of items that
481 are interpreted according to the I/O conversion rules for the array's
482 element type, plus decoration that indicates the array structure.
483 The decoration consists of curly braces (<literal>{</> and <literal>}</>)
484 around the array value plus delimiter characters between adjacent items.
485 The delimiter character is usually a comma (<literal>,</>) but can be
486 something else: it is determined by the <literal>typdelim</> setting
487 for the array's element type. (Among the standard data types provided
488 in the <productname>PostgreSQL</productname> distribution, type
489 <literal>box</> uses a semicolon (<literal>;</>) but all the others
490 use comma.) In a multidimensional array, each dimension (row, plane,
491 cube, etc.) gets its own level of curly braces, and delimiters
492 must be written between adjacent curly-braced entities of the same level.
493 You may write whitespace before a left brace, after a right
494 brace, or before any individual item string. Whitespace after an item
495 is not ignored, however: after skipping leading whitespace, everything
496 up to the next right brace or delimiter is taken as the item value.
500 As illustrated earlier in this chapter, arrays may also be represented
501 using the <command>ARRAY</command> expression syntax. This representation
502 of an array value consists of items that are interpreted according to the
503 I/O conversion rules for the array's element type, plus decoration that
504 indicates the array structure. The decoration consists of the keyword
505 <command>ARRAY</command> and square brackets (<literal>[</> and
506 <literal>]</>) around the array values, plus delimiter characters between
507 adjacent items. The delimiter character is always a comma (<literal>,</>).
508 When representing multidimensional arrays, the keyword
509 <command>ARRAY</command> is only necessary for the outer level. For example,
510 <literal>'{{"hello world", "happy birthday"}}'</literal> could be written as:
512 SELECT ARRAY[['hello world', 'happy birthday']];
514 ------------------------------------
515 {{"hello world","happy birthday"}}
518 or it also could be written as:
520 SELECT ARRAY[ARRAY['hello world', 'happy birthday']];
522 ------------------------------------
523 {{"hello world","happy birthday"}}
529 A final method to represent an array, is through an
530 <command>ARRAY</command> sub-select expression. For example:
532 SELECT ARRAY(SELECT oid FROM pg_proc WHERE proname LIKE 'bytea%');
534 -------------------------------------------------------------
535 {2011,1954,1948,1952,1951,1244,1950,2005,1949,1953,2006,31}
538 The sub-select may <emphasis>only</emphasis> return a single column. The
539 resulting one-dimensional array will have an element for each row in the
540 sub-select result, with an element type matching that of the sub-select's
545 Arrays may be cast from one type to another in similar fashion to other
549 SELECT ARRAY[1,2,3]::oid[];
555 SELECT CAST(ARRAY[1,2,3] AS float8[]);
567 <title>Quoting Array Elements</title>
570 As shown above, when writing an array value you may write double
571 quotes around any individual array
572 element. You <emphasis>must</> do so if the element value would otherwise
573 confuse the array-value parser. For example, elements containing curly
574 braces, commas (or whatever the delimiter character is), double quotes,
575 backslashes, or leading white space must be double-quoted. To put a double
576 quote or backslash in an array element value, precede it with a backslash.
577 Alternatively, you can use backslash-escaping to protect all data characters
578 that would otherwise be taken as array syntax or ignorable white space.
583 The discussion in the preceding paragraph with respect to double quoting does
584 not pertain to the <command>ARRAY</command> expression syntax. In that case,
585 each element is quoted exactly as any other literal value of the element type.
590 The array output routine will put double quotes around element values
591 if they are empty strings or contain curly braces, delimiter characters,
592 double quotes, backslashes, or white space. Double quotes and backslashes
593 embedded in element values will be backslash-escaped. For numeric
594 data types it is safe to assume that double quotes will never appear, but
595 for textual data types one should be prepared to cope with either presence
596 or absence of quotes. (This is a change in behavior from pre-7.2
597 <productname>PostgreSQL</productname> releases.)
602 Remember that what you write in an SQL command will first be interpreted
603 as a string literal, and then as an array. This doubles the number of
604 backslashes you need. For example, to insert a <type>text</> array
605 value containing a backslash and a double quote, you'd need to write
607 INSERT ... VALUES ('{"\\\\","\\""}');
609 The string-literal processor removes one level of backslashes, so that
610 what arrives at the array-value parser looks like <literal>{"\\","\""}</>.
611 In turn, the strings fed to the <type>text</> data type's input routine
612 become <literal>\</> and <literal>"</> respectively. (If we were working
613 with a data type whose input routine also treated backslashes specially,
614 <type>bytea</> for example, we might need as many as eight backslashes
615 in the command to get one backslash into the stored array element.)