1 <!-- $PostgreSQL: pgsql/doc/src/sgml/array.sgml,v 1.69 2009/04/27 16:27:35 momjian 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 ignores any supplied array size
58 limits, i.e., the behavior is the same as for arrays of unspecified
63 In addition, 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 the 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 by using
73 they keyword <literal>ARRAY</>, can
74 be used for one-dimensional arrays;
75 <structfield>pay_by_quarter</structfield> could have been defined
78 pay_by_quarter integer ARRAY[4],
80 Or, if no array size is to be specified:
82 pay_by_quarter integer ARRAY,
84 As before, however, <productname>PostgreSQL</> does not enforce the
85 size restriction in any case.
89 <sect2 id="arrays-input">
90 <title>Array Value Input</title>
93 <primary>array</primary>
94 <secondary>constant</secondary>
98 To write an array value as a literal constant, enclose the element
99 values within curly braces and separate them by commas. (If you
100 know C, this is not unlike the C syntax for initializing
101 structures.) You can put double quotes around any element value,
102 and must do so if it contains commas or curly braces. (More
103 details appear below.) Thus, the general format of an array
104 constant is the following:
106 '{ <replaceable>val1</replaceable> <replaceable>delim</replaceable> <replaceable>val2</replaceable> <replaceable>delim</replaceable> ... }'
108 where <replaceable>delim</replaceable> is the delimiter character
109 for the type, as recorded in its <literal>pg_type</literal> entry.
110 Among the standard data types provided in the
111 <productname>PostgreSQL</productname> distribution, all use a comma
112 (<literal>,</>), except for the type <literal>box</> which uses a semicolon
113 (<literal>;</>). Each <replaceable>val</replaceable> is
114 either a constant of the array element type, or a subarray. An example
115 of an array constant is:
117 '{{1,2,3},{4,5,6},{7,8,9}}'
119 This constant is a two-dimensional, 3-by-3 array consisting of
120 three subarrays of integers.
124 To set an element of an array to NULL, write <literal>NULL</>
125 for the element value. (Any upper- or lower-case variant of
126 <literal>NULL</> will do.) If you want an actual string value
127 <quote>NULL</>, you must put double quotes around it.
131 (These kinds of array constants are actually only a special case of
132 the generic type constants discussed in <xref
133 linkend="sql-syntax-constants-generic">. The constant is initially
134 treated as a string and passed to the array input conversion
135 routine. An explicit type specification might be necessary.)
139 Now we can show some <command>INSERT</command> statements:
144 '{10000, 10000, 10000, 10000}',
145 '{{"meeting", "lunch"}, {"training", "presentation"}}');
149 '{20000, 25000, 25000, 25000}',
150 '{{"breakfast", "consulting"}, {"meeting", "lunch"}}');
155 The result of the previous two inserts looks like this:
158 SELECT * FROM sal_emp;
159 name | pay_by_quarter | schedule
160 -------+---------------------------+-------------------------------------------
161 Bill | {10000,10000,10000,10000} | {{meeting,lunch},{training,presentation}}
162 Carol | {20000,25000,25000,25000} | {{breakfast,consulting},{meeting,lunch}}
168 Multidimensional arrays must have matching extents for each
169 dimension. A mismatch causes an error, for example:
174 '{10000, 10000, 10000, 10000}',
175 '{{"meeting", "lunch"}, {"meeting"}}');
176 ERROR: multidimensional arrays must have array expressions with matching dimensions
181 The <literal>ARRAY</> constructor syntax can also be used:
185 ARRAY[10000, 10000, 10000, 10000],
186 ARRAY[['meeting', 'lunch'], ['training', 'presentation']]);
190 ARRAY[20000, 25000, 25000, 25000],
191 ARRAY[['breakfast', 'consulting'], ['meeting', 'lunch']]);
193 Notice that the array elements are ordinary SQL constants or
194 expressions; for instance, string literals are single quoted, instead of
195 double quoted as they would be in an array literal. The <literal>ARRAY</>
196 constructor syntax is discussed in more detail in
197 <xref linkend="sql-syntax-array-constructors">.
201 <sect2 id="arrays-accessing">
202 <title>Accessing Arrays</title>
205 <primary>array</primary>
206 <secondary>accessing</secondary>
210 Now, we can run some queries on the table.
211 First, we show how to access a single element of an array.
212 This query retrieves the names of the employees whose pay changed in
216 SELECT name FROM sal_emp WHERE pay_by_quarter[1] <> pay_by_quarter[2];
224 The array subscript numbers are written within square brackets.
225 By default <productname>PostgreSQL</productname> uses a
226 one-based numbering convention for arrays, that is,
227 an array of <replaceable>n</> elements starts with <literal>array[1]</literal> and
228 ends with <literal>array[<replaceable>n</>]</literal>.
232 This query retrieves the third quarter pay of all employees:
235 SELECT pay_by_quarter[3] FROM sal_emp;
246 We can also access arbitrary rectangular slices of an array, or
247 subarrays. An array slice is denoted by writing
248 <literal><replaceable>lower-bound</replaceable>:<replaceable>upper-bound</replaceable></literal>
249 for one or more array dimensions. For example, this query retrieves the first
250 item on Bill's schedule for the first two days of the week:
253 SELECT schedule[1:2][1:1] FROM sal_emp WHERE name = 'Bill';
256 ------------------------
257 {{meeting},{training}}
261 If any dimension is written as a slice, i.e., contains a colon, then all
262 dimensions are treated as slices. Any dimension that has only a single
263 number (no colon) is treated as being from <literal>1</>
264 to the number specified. For example, <literal>[2]</> is treated as
265 <literal>[1:2]</>, as in this example:
268 SELECT schedule[1:2][2] FROM sal_emp WHERE name = 'Bill';
271 -------------------------------------------
272 {{meeting,lunch},{training,presentation}}
276 To avoid confusion with the non-slice case, it's best to use slice syntax
277 for all dimensions, e.g., <literal>[1:2][1:1]</>, not <literal>[2][1:1]</>.
281 An array subscript expression will return null if either the array itself or
282 any of the subscript expressions are null. Also, null is returned if a
283 subscript is outside the array bounds (this case does not raise an error).
284 For example, if <literal>schedule</>
285 currently has the dimensions <literal>[1:3][1:2]</> then referencing
286 <literal>schedule[3][3]</> yields NULL. Similarly, an array reference
287 with the wrong number of subscripts yields a null rather than an error.
291 An array slice expression likewise yields null if the array itself or
292 any of the subscript expressions are null. However, in other
293 cases such as selecting an array slice that
294 is completely outside the current array bounds, a slice expression
295 yields an empty (zero-dimensional) array instead of null. (This
296 does not match non-slice behavior and is done for historical reasons.)
297 If the requested slice partially overlaps the array bounds, then it
298 is silently reduced to just the overlapping region instead of
303 The current dimensions of any array value can be retrieved with the
304 <function>array_dims</function> function:
307 SELECT array_dims(schedule) FROM sal_emp WHERE name = 'Carol';
315 <function>array_dims</function> produces a <type>text</type> result,
316 which is convenient for people to read but perhaps inconvenient
317 for programs. Dimensions can also be retrieved with
318 <function>array_upper</function> and <function>array_lower</function>,
319 which return the upper and lower bound of a
320 specified array dimension, respectively:
323 SELECT array_upper(schedule, 1) FROM sal_emp WHERE name = 'Carol';
331 <function>array_length</function> will return the length of a specified
335 SELECT array_length(schedule, 1) FROM sal_emp WHERE name = 'Carol';
345 <sect2 id="arrays-modifying">
346 <title>Modifying Arrays</title>
349 <primary>array</primary>
350 <secondary>modifying</secondary>
354 An array value can be replaced completely:
357 UPDATE sal_emp SET pay_by_quarter = '{25000,25000,27000,27000}'
358 WHERE name = 'Carol';
361 or using the <literal>ARRAY</literal> expression syntax:
364 UPDATE sal_emp SET pay_by_quarter = ARRAY[25000,25000,27000,27000]
365 WHERE name = 'Carol';
368 An array can also be updated at a single element:
371 UPDATE sal_emp SET pay_by_quarter[4] = 15000
375 or updated in a slice:
378 UPDATE sal_emp SET pay_by_quarter[1:2] = '{27000,27000}'
379 WHERE name = 'Carol';
385 A stored array value can be enlarged by assigning to elements not already
386 present. Any positions between those previously present and the newly
387 assigned elements will be filled with nulls. For example, if array
388 <literal>myarray</> currently has 4 elements, it will have six
389 elements after an update that assigns to <literal>myarray[6]</>;
390 <literal>myarray[5]</> will contain null.
391 Currently, enlargement in this fashion is only allowed for one-dimensional
392 arrays, not multidimensional arrays.
396 Subscripted assignment allows creation of arrays that do not use one-based
397 subscripts. For example one might assign to <literal>myarray[-2:7]</> to
398 create an array with subscript values from -2 to 7.
402 New array values can also be constructed using the concatenation operator,
403 <literal>||</literal>:
405 SELECT ARRAY[1,2] || ARRAY[3,4];
411 SELECT ARRAY[5,6] || ARRAY[[1,2],[3,4]];
413 ---------------------
420 The concatenation operator allows a single element to be pushed to the
421 beginning or end of a one-dimensional array. It also accepts two
422 <replaceable>N</>-dimensional arrays, or an <replaceable>N</>-dimensional
423 and an <replaceable>N+1</>-dimensional array.
427 When a single element is pushed to either the beginning or end of a
428 one-dimensional array, the result is an array with the same lower bound
429 subscript as the array operand. For example:
431 SELECT array_dims(1 || '[0:1]={2,3}'::int[]);
437 SELECT array_dims(ARRAY[1,2] || 3);
446 When two arrays with an equal number of dimensions are concatenated, the
447 result retains the lower bound subscript of the left-hand operand's outer
448 dimension. The result is an array comprising every element of the left-hand
449 operand followed by every element of the right-hand operand. For example:
451 SELECT array_dims(ARRAY[1,2] || ARRAY[3,4,5]);
457 SELECT array_dims(ARRAY[[1,2],[3,4]] || ARRAY[[5,6],[7,8],[9,0]]);
466 When an <replaceable>N</>-dimensional array is pushed to the beginning
467 or end of an <replaceable>N+1</>-dimensional array, the result is
468 analogous to the element-array case above. Each <replaceable>N</>-dimensional
469 sub-array is essentially an element of the <replaceable>N+1</>-dimensional
470 array's outer dimension. For example:
472 SELECT array_dims(ARRAY[1,2] || ARRAY[[3,4],[5,6]]);
481 An array can also be constructed by using the functions
482 <function>array_prepend</function>, <function>array_append</function>,
483 or <function>array_cat</function>. The first two only support one-dimensional
484 arrays, but <function>array_cat</function> supports multidimensional arrays.
486 Note that the concatenation operator discussed above is preferred over
487 direct use of these functions. In fact, these functions primarily exist for use
488 in implementing the concatenation operator. However, they might be directly
489 useful in the creation of user-defined aggregates. Some examples:
492 SELECT array_prepend(1, ARRAY[2,3]);
498 SELECT array_append(ARRAY[1,2], 3);
504 SELECT array_cat(ARRAY[1,2], ARRAY[3,4]);
510 SELECT array_cat(ARRAY[[1,2],[3,4]], ARRAY[5,6]);
512 ---------------------
516 SELECT array_cat(ARRAY[5,6], ARRAY[[1,2],[3,4]]);
518 ---------------------
524 <sect2 id="arrays-searching">
525 <title>Searching in Arrays</title>
528 <primary>array</primary>
529 <secondary>searching</secondary>
533 To search for a value in an array, each value must be checked.
534 This can be done manually, if you know the size of the array.
538 SELECT * FROM sal_emp WHERE pay_by_quarter[1] = 10000 OR
539 pay_by_quarter[2] = 10000 OR
540 pay_by_quarter[3] = 10000 OR
541 pay_by_quarter[4] = 10000;
544 However, this quickly becomes tedious for large arrays, and is not
545 helpful if the size of the array is unknown. An alternative method is
546 described in <xref linkend="functions-comparisons">. The above
547 query could be replaced by:
550 SELECT * FROM sal_emp WHERE 10000 = ANY (pay_by_quarter);
553 In addition, you can find rows where the array has all values
557 SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter);
563 Alternatively, the <function>generate_subscripts</> function can be used.
568 (SELECT pay_by_quarter,
569 generate_subscripts(pay_by_quarter, 1) AS s
571 WHERE pay_by_quarter[s] = 10000;
574 This function is described in <xref linkend="functions-srf-subscripts">.
579 Arrays are not sets; searching for specific array elements
580 can be a sign of database misdesign. Consider
581 using a separate table with a row for each item that would be an
582 array element. This will be easier to search, and is likely to
583 scale better for a large number of elements.
588 <sect2 id="arrays-io">
589 <title>Array Input and Output Syntax</title>
592 <primary>array</primary>
593 <secondary>I/O</secondary>
597 The external text representation of an array value consists of items that
598 are interpreted according to the I/O conversion rules for the array's
599 element type, plus decoration that indicates the array structure.
600 The decoration consists of curly braces (<literal>{</> and <literal>}</>)
601 around the array value plus delimiter characters between adjacent items.
602 The delimiter character is usually a comma (<literal>,</>) but can be
603 something else: it is determined by the <literal>typdelim</> setting
604 for the array's element type. (Among the standard data types provided
605 in the <productname>PostgreSQL</productname> distribution, all
606 use a comma, except for <literal>box</>, which uses a semicolon (<literal>;</>).)
607 In a multidimensional array, each dimension (row, plane,
608 cube, etc.) gets its own level of curly braces, and delimiters
609 must be written between adjacent curly-braced entities of the same level.
613 The array output routine will put double quotes around element values
614 if they are empty strings, contain curly braces, delimiter characters,
615 double quotes, backslashes, or white space, or match the word
616 <literal>NULL</>. Double quotes and backslashes
617 embedded in element values will be backslash-escaped. For numeric
618 data types it is safe to assume that double quotes will never appear, but
619 for textual data types one should be prepared to cope with either the presence
620 or absence of quotes.
624 By default, the lower bound index value of an array's dimensions is
625 set to one. To represent arrays with other lower bounds, the array
626 subscript ranges can be specified explicitly before writing the
628 This decoration consists of square brackets (<literal>[]</>)
629 around each array dimension's lower and upper bounds, with
630 a colon (<literal>:</>) delimiter character in between. The
631 array dimension decoration is followed by an equal sign (<literal>=</>).
634 SELECT f1[1][-2][3] AS e1, f1[1][-1][5] AS e2
635 FROM (SELECT '[1:1][-2:-1][3:5]={{{1,2,3},{4,5,6}}}'::int[] AS f1) AS ss;
642 The array output routine will include explicit dimensions in its result
643 only when there are one or more lower bounds different from one.
647 If the value written for an element is <literal>NULL</> (in any case
648 variant), the element is taken to be NULL. The presence of any quotes
649 or backslashes disables this and allows the literal string value
650 <quote>NULL</> to be entered. Also, for backwards compatibility with
651 pre-8.2 versions of <productname>PostgreSQL</>, the <xref
652 linkend="guc-array-nulls"> configuration parameter can be turned
653 <literal>off</> to suppress recognition of <literal>NULL</> as a NULL.
657 As shown previously, when writing an array value you can use double
658 quotes around any individual array element. You <emphasis>must</> do so
659 if the element value would otherwise confuse the array-value parser.
660 For example, elements containing curly braces, commas (or the matching
661 delimiter character), double quotes, backslashes, or leading or trailing
662 whitespace must be double-quoted. Empty strings and strings matching the
663 word <literal>NULL</> must be quoted, too. To put a double quote or
664 backslash in a quoted array element value, use escape string syntax
665 and precede it with a backslash. Alternatively, you can avoid quotes and use
666 backslash-escaping to protect all data characters that would otherwise
667 be taken as array syntax.
671 You can use whitespace before a left brace or after a right
672 brace. You can also add whitespace before or after any individual item
673 string. In all of these cases the whitespace will be ignored. However,
674 whitespace within double-quoted elements, or surrounded on both sides by
675 non-whitespace characters of an element, is not ignored.
680 Remember that what you write in an SQL command will first be interpreted
681 as a string literal, and then as an array. This doubles the number of
682 backslashes you need. For example, to insert a <type>text</> array
683 value containing a backslash and a double quote, you'd need to write:
685 INSERT ... VALUES (E'{"\\\\","\\""}');
687 The escape string processor removes one level of backslashes, so that
688 what arrives at the array-value parser looks like <literal>{"\\","\""}</>.
689 In turn, the strings fed to the <type>text</> data type's input routine
690 become <literal>\</> and <literal>"</> respectively. (If we were working
691 with a data type whose input routine also treated backslashes specially,
692 <type>bytea</> for example, we might need as many as eight backslashes
693 in the command to get one backslash into the stored array element.)
694 Dollar quoting (see <xref linkend="sql-syntax-dollar-quoting">) can be
695 used to avoid the need to double backslashes.
701 The <literal>ARRAY</> constructor syntax (see
702 <xref linkend="sql-syntax-array-constructors">) is often easier to work
703 with than the array-literal syntax when writing array values in SQL
704 commands. In <literal>ARRAY</>, individual element values are written the
705 same way they would be written when not members of an array.