1 <!-- $PostgreSQL: pgsql/doc/src/sgml/array.sgml,v 1.58 2007/02/20 14:54:47 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 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 run-time behavior.
66 An alternative syntax, which conforms to the SQL standard, can
67 be used for one-dimensional arrays.
68 <structfield>pay_by_quarter</structfield> could have been defined
71 pay_by_quarter integer ARRAY[4],
73 This syntax requires an integer constant to denote the array size.
74 As before, however, <productname>PostgreSQL</> does not enforce the
80 <title>Array Value Input</title>
83 <primary>array</primary>
84 <secondary>constant</secondary>
88 To write an array value as a literal constant, enclose the element
89 values within curly braces and separate them by commas. (If you
90 know C, this is not unlike the C syntax for initializing
91 structures.) You can put double quotes around any element value,
92 and must do so if it contains commas or curly braces. (More
93 details appear below.) Thus, the general format of an array
94 constant is the following:
96 '{ <replaceable>val1</replaceable> <replaceable>delim</replaceable> <replaceable>val2</replaceable> <replaceable>delim</replaceable> ... }'
98 where <replaceable>delim</replaceable> is the delimiter character
99 for the type, as recorded in its <literal>pg_type</literal> entry.
100 Among the standard data types provided in the
101 <productname>PostgreSQL</productname> distribution, type
102 <literal>box</> uses a semicolon (<literal>;</>) but all the others
103 use comma (<literal>,</>). Each <replaceable>val</replaceable> is
104 either a constant of the array element type, or a subarray. An example
105 of an array constant is:
107 '{{1,2,3},{4,5,6},{7,8,9}}'
109 This constant is a two-dimensional, 3-by-3 array consisting of
110 three subarrays of integers.
114 To set an element of an array constant to NULL, write <literal>NULL</>
115 for the element value. (Any upper- or lower-case variant of
116 <literal>NULL</> will do.) If you want an actual string value
117 <quote>NULL</>, you must put double quotes around it.
121 (These kinds of array constants are actually only a special case of
122 the generic type constants discussed in <xref
123 linkend="sql-syntax-constants-generic">. The constant is initially
124 treated as a string and passed to the array input conversion
125 routine. An explicit type specification might be necessary.)
129 Now we can show some <command>INSERT</command> statements:
134 '{10000, 10000, 10000, 10000}',
135 '{{"meeting", "lunch"}, {"training", "presentation"}}');
139 '{20000, 25000, 25000, 25000}',
140 '{{"breakfast", "consulting"}, {"meeting", "lunch"}}');
145 The result of the previous two inserts looks like this:
148 SELECT * FROM sal_emp;
149 name | pay_by_quarter | schedule
150 -------+---------------------------+-------------------------------------------
151 Bill | {10000,10000,10000,10000} | {{meeting,lunch},{training,presentation}}
152 Carol | {20000,25000,25000,25000} | {{breakfast,consulting},{meeting,lunch}}
158 The <literal>ARRAY</> constructor syntax can also be used:
162 ARRAY[10000, 10000, 10000, 10000],
163 ARRAY[['meeting', 'lunch'], ['training', 'presentation']]);
167 ARRAY[20000, 25000, 25000, 25000],
168 ARRAY[['breakfast', 'consulting'], ['meeting', 'lunch']]);
170 Notice that the array elements are ordinary SQL constants or
171 expressions; for instance, string literals are single quoted, instead of
172 double quoted as they would be in an array literal. The <literal>ARRAY</>
173 constructor syntax is discussed in more detail in
174 <xref linkend="sql-syntax-array-constructors">.
178 Multidimensional arrays must have matching extents for each
179 dimension. A mismatch causes an error report, for example:
184 '{10000, 10000, 10000, 10000}',
185 '{{"meeting", "lunch"}, {"meeting"}}');
186 ERROR: multidimensional arrays must have array expressions with matching dimensions
192 <title>Accessing Arrays</title>
195 Now, we can run some queries on the table.
196 First, we show how to access a single element of an array at a time.
197 This query retrieves the names of the employees whose pay changed in
201 SELECT name FROM sal_emp WHERE pay_by_quarter[1] <> pay_by_quarter[2];
209 The array subscript numbers are written within square brackets.
210 By default <productname>PostgreSQL</productname> uses the
211 one-based numbering convention for arrays, that is,
212 an array of <replaceable>n</> elements starts with <literal>array[1]</literal> and
213 ends with <literal>array[<replaceable>n</>]</literal>.
217 This query retrieves the third quarter pay of all employees:
220 SELECT pay_by_quarter[3] FROM sal_emp;
231 We can also access arbitrary rectangular slices of an array, or
232 subarrays. An array slice is denoted by writing
233 <literal><replaceable>lower-bound</replaceable>:<replaceable>upper-bound</replaceable></literal>
234 for one or more array dimensions. For example, this query retrieves the first
235 item on Bill's schedule for the first two days of the week:
238 SELECT schedule[1:2][1:1] FROM sal_emp WHERE name = 'Bill';
241 ------------------------
242 {{meeting},{training}}
246 If any dimension is written as a slice, i.e contains a colon, then all
247 dimensions are treated as slices. If a dimension is missing, it is
248 assumed to be <literal>[1:1]</>. If a dimension has only a single
249 number (no colon), that dimension is treated as being from <literal>1</>
250 to the number specified. For example, <literal>[2]</> is treated as
251 <literal>[1:2], as in this example:
254 SELECT schedule[1:2][2] FROM sal_emp WHERE name = 'Bill';
257 -------------------------------------------
258 {{meeting,lunch},{training,presentation}}
264 An array subscript expression will return null if either the array itself or
265 any of the subscript expressions are null. Also, null is returned if a
266 subscript is outside the array bounds (this case does not raise an error).
267 For example, if <literal>schedule</>
268 currently has the dimensions <literal>[1:3][1:2]</> then referencing
269 <literal>schedule[3][3]</> yields NULL. Similarly, an array reference
270 with the wrong number of subscripts yields a null rather than an error.
274 An array slice expression likewise yields null if the array itself or
275 any of the subscript expressions are null. However, in other corner
276 cases such as selecting an array slice that
277 is completely outside the current array bounds, a slice expression
278 yields an empty (zero-dimensional) array instead of null.
279 If the requested slice partially overlaps the array bounds, then it
280 is silently reduced to just the overlapping region.
284 The current dimensions of any array value can be retrieved with the
285 <function>array_dims</function> function:
288 SELECT array_dims(schedule) FROM sal_emp WHERE name = 'Carol';
296 <function>array_dims</function> produces a <type>text</type> result,
297 which is convenient for people to read but perhaps not so convenient
298 for programs. Dimensions can also be retrieved with
299 <function>array_upper</function> and <function>array_lower</function>,
300 which return the upper and lower bound of a
301 specified array dimension, respectively:
304 SELECT array_upper(schedule, 1) FROM sal_emp WHERE name = 'Carol';
315 <title>Modifying Arrays</title>
318 An array value can be replaced completely:
321 UPDATE sal_emp SET pay_by_quarter = '{25000,25000,27000,27000}'
322 WHERE name = 'Carol';
325 or using the <literal>ARRAY</literal> expression syntax:
328 UPDATE sal_emp SET pay_by_quarter = ARRAY[25000,25000,27000,27000]
329 WHERE name = 'Carol';
332 An array can also be updated at a single element:
335 UPDATE sal_emp SET pay_by_quarter[4] = 15000
339 or updated in a slice:
342 UPDATE sal_emp SET pay_by_quarter[1:2] = '{27000,27000}'
343 WHERE name = 'Carol';
349 A stored array value can be enlarged by assigning to element(s) not already
350 present. Any positions between those previously present and the newly
351 assigned element(s) will be filled with nulls. For example, if array
352 <literal>myarray</> currently has 4 elements, it will have six
353 elements after an update that assigns to <literal>myarray[6]</>,
354 and <literal>myarray[5]</> will contain a null.
355 Currently, enlargement in this fashion is only allowed for one-dimensional
356 arrays, not multidimensional arrays.
360 Subscripted assignment allows creation of arrays that do not use one-based
361 subscripts. For example one might assign to <literal>myarray[-2:7]</> to
362 create an array with subscript values running from -2 to 7.
366 New array values can also be constructed by using the concatenation operator,
367 <literal>||</literal>:
369 SELECT ARRAY[1,2] || ARRAY[3,4];
375 SELECT ARRAY[5,6] || ARRAY[[1,2],[3,4]];
377 ---------------------
384 The concatenation operator allows a single element to be pushed on to the
385 beginning or end of a one-dimensional array. It also accepts two
386 <replaceable>N</>-dimensional arrays, or an <replaceable>N</>-dimensional
387 and an <replaceable>N+1</>-dimensional array.
391 When a single element is pushed on to either the beginning or end of a
392 one-dimensional array, the result is an array with the same lower bound
393 subscript as the array operand. For example:
395 SELECT array_dims(1 || '[0:1]={2,3}'::int[]);
401 SELECT array_dims(ARRAY[1,2] || 3);
410 When two arrays with an equal number of dimensions are concatenated, the
411 result retains the lower bound subscript of the left-hand operand's outer
412 dimension. The result is an array comprising every element of the left-hand
413 operand followed by every element of the right-hand operand. For example:
415 SELECT array_dims(ARRAY[1,2] || ARRAY[3,4,5]);
421 SELECT array_dims(ARRAY[[1,2],[3,4]] || ARRAY[[5,6],[7,8],[9,0]]);
430 When an <replaceable>N</>-dimensional array is pushed on to the beginning
431 or end of an <replaceable>N+1</>-dimensional array, the result is
432 analogous to the element-array case above. Each <replaceable>N</>-dimensional
433 sub-array is essentially an element of the <replaceable>N+1</>-dimensional
434 array's outer dimension. For example:
436 SELECT array_dims(ARRAY[1,2] || ARRAY[[3,4],[5,6]]);
445 An array can also be constructed by using the functions
446 <function>array_prepend</function>, <function>array_append</function>,
447 or <function>array_cat</function>. The first two only support one-dimensional
448 arrays, but <function>array_cat</function> supports multidimensional arrays.
450 Note that the concatenation operator discussed above is preferred over
451 direct use of these functions. In fact, the functions exist primarily for use
452 in implementing the concatenation operator. However, they might be directly
453 useful in the creation of user-defined aggregates. Some examples:
456 SELECT array_prepend(1, ARRAY[2,3]);
462 SELECT array_append(ARRAY[1,2], 3);
468 SELECT array_cat(ARRAY[1,2], ARRAY[3,4]);
474 SELECT array_cat(ARRAY[[1,2],[3,4]], ARRAY[5,6]);
476 ---------------------
480 SELECT array_cat(ARRAY[5,6], ARRAY[[1,2],[3,4]]);
482 ---------------------
489 <title>Searching in Arrays</title>
492 To search for a value in an array, you must check each value of the
493 array. This can be done by hand, if you know the size of the array.
497 SELECT * FROM sal_emp WHERE pay_by_quarter[1] = 10000 OR
498 pay_by_quarter[2] = 10000 OR
499 pay_by_quarter[3] = 10000 OR
500 pay_by_quarter[4] = 10000;
503 However, this quickly becomes tedious for large arrays, and is not
504 helpful if the size of the array is uncertain. An alternative method is
505 described in <xref linkend="functions-comparisons">. The above
506 query could be replaced by:
509 SELECT * FROM sal_emp WHERE 10000 = ANY (pay_by_quarter);
512 In addition, you could find rows where the array had all values
516 SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter);
523 Arrays are not sets; searching for specific array elements
524 can be a sign of database misdesign. Consider
525 using a separate table with a row for each item that would be an
526 array element. This will be easier to search, and is likely to
527 scale up better to large numbers of elements.
533 <title>Array Input and Output Syntax</title>
536 The external text representation of an array value consists of items that
537 are interpreted according to the I/O conversion rules for the array's
538 element type, plus decoration that indicates the array structure.
539 The decoration consists of curly braces (<literal>{</> and <literal>}</>)
540 around the array value plus delimiter characters between adjacent items.
541 The delimiter character is usually a comma (<literal>,</>) but can be
542 something else: it is determined by the <literal>typdelim</> setting
543 for the array's element type. (Among the standard data types provided
544 in the <productname>PostgreSQL</productname> distribution, type
545 <literal>box</> uses a semicolon (<literal>;</>) but all the others
546 use comma.) In a multidimensional array, each dimension (row, plane,
547 cube, etc.) gets its own level of curly braces, and delimiters
548 must be written between adjacent curly-braced entities of the same level.
552 The array output routine will put double quotes around element values
553 if they are empty strings, contain curly braces, delimiter characters,
554 double quotes, backslashes, or white space, or match the word
555 <literal>NULL</>. Double quotes and backslashes
556 embedded in element values will be backslash-escaped. For numeric
557 data types it is safe to assume that double quotes will never appear, but
558 for textual data types one should be prepared to cope with either presence
559 or absence of quotes.
563 By default, the lower bound index value of an array's dimensions is
564 set to one. To represent arrays with other lower bounds, the array
565 subscript ranges can be specified explicitly before writing the
567 This decoration consists of square brackets (<literal>[]</>)
568 around each array dimension's lower and upper bounds, with
569 a colon (<literal>:</>) delimiter character in between. The
570 array dimension decoration is followed by an equal sign (<literal>=</>).
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;
581 The array output routine will include explicit dimensions in its result
582 only when there are one or more lower bounds different from one.
586 If the value written for an element is <literal>NULL</> (in any case
587 variant), the element is taken to be NULL. The presence of any quotes
588 or backslashes disables this and allows the literal string value
589 <quote>NULL</> to be entered. Also, for backwards compatibility with
590 pre-8.2 versions of <productname>PostgreSQL</>, the <xref
591 linkend="guc-array-nulls"> configuration parameter might be turned
592 <literal>off</> to suppress recognition of <literal>NULL</> as a NULL.
596 As shown previously, when writing an array value you can write double
597 quotes around any individual array element. You <emphasis>must</> do so
598 if the element value would otherwise confuse the array-value parser.
599 For example, elements containing curly braces, commas (or whatever the
600 delimiter character is), double quotes, backslashes, or leading or trailing
601 whitespace must be double-quoted. Empty strings and strings matching the
602 word <literal>NULL</> must be quoted, too. To put a double quote or
603 backslash in a quoted array element value, use escape string syntax
604 and precede it with a backslash. Alternatively, you can use
605 backslash-escaping to protect all data characters that would otherwise
606 be taken as array syntax.
610 You can write whitespace before a left brace or after a right
611 brace. You can also write whitespace before or after any individual item
612 string. In all of these cases the whitespace will be ignored. However,
613 whitespace within double-quoted elements, or surrounded on both sides by
614 non-whitespace characters of an element, is not ignored.
619 Remember that what you write in an SQL command will first be interpreted
620 as a string literal, and then as an array. This doubles the number of
621 backslashes you need. For example, to insert a <type>text</> array
622 value containing a backslash and a double quote, you'd need to write:
624 INSERT ... VALUES (E'{"\\\\","\\""}');
626 The escape string processor removes one level of backslashes, so that
627 what arrives at the array-value parser looks like <literal>{"\\","\""}</>.
628 In turn, the strings fed to the <type>text</> data type's input routine
629 become <literal>\</> and <literal>"</> respectively. (If we were working
630 with a data type whose input routine also treated backslashes specially,
631 <type>bytea</> for example, we might need as many as eight backslashes
632 in the command to get one backslash into the stored array element.)
633 Dollar quoting (see <xref linkend="sql-syntax-dollar-quoting">) can be
634 used to avoid the need to double backslashes.
640 The <literal>ARRAY</> constructor syntax (see
641 <xref linkend="sql-syntax-array-constructors">) is often easier to work
642 with than the array-literal syntax when writing array values in SQL
643 commands. In <literal>ARRAY</>, individual element values are written the
644 same way they would be written when not members of an array.