1- <!-- $PostgreSQL: pgsql/doc/src/sgml/array.sgml,v 1.68 2008/11/12 13:09:27 petere Exp $ -->
1+ <!-- $PostgreSQL: pgsql/doc/src/sgml/array.sgml,v 1.69 2009/04/27 16:27:35 momjian Exp $ -->
22
33<sect1 id="arrays">
44 <title>Arrays</title>
@@ -54,23 +54,24 @@ CREATE TABLE tictactoe (
5454);
5555</programlisting>
5656
57- However, the current implementationdoes not enforce the array size
58- limits — the behavior is the same as for arrays of unspecified
57+ However, the current implementationignores any supplied array size
58+ limits, i.e., the behavior is the same as for arrays of unspecified
5959 length.
6060 </para>
6161
6262 <para>
63- Actually , the current implementation does not enforce the declared
63+ In addition , the current implementation does not enforce the declared
6464 number of dimensions either. Arrays of a particular element type are
6565 all considered to be of the same type, regardless of size or number
66- of dimensions. So, declaring number of dimensions or sizes in
66+ of dimensions. So, declaringthe number of dimensions or sizes in
6767 <command>CREATE TABLE</command> is simply documentation, it does not
6868 affect run-time behavior.
6969 </para>
7070
7171 <para>
72- An alternative syntax, which conforms to the SQL standard, can
73- be used for one-dimensional arrays.
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;
7475 <structfield>pay_by_quarter</structfield> could have been defined
7576 as:
7677<programlisting>
@@ -107,9 +108,9 @@ CREATE TABLE tictactoe (
107108 where <replaceable>delim</replaceable> is the delimiter character
108109 for the type, as recorded in its <literal>pg_type</literal> entry.
109110 Among the standard data types provided in the
110- <productname>PostgreSQL</productname> distribution,type
111- <literal>box </> uses a semicolon ( <literal>; </>) but all the others
112- use comma (<literal>, </>). Each <replaceable>val</replaceable> is
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
113114 either a constant of the array element type, or a subarray. An example
114115 of an array constant is:
115116<programlisting>
@@ -120,7 +121,7 @@ CREATE TABLE tictactoe (
120121 </para>
121122
122123 <para>
123- To set an element of an arrayconstant to NULL, write <literal>NULL</>
124+ To set an element of an array to NULL, write <literal>NULL</>
124125 for the element value. (Any upper- or lower-case variant of
125126 <literal>NULL</> will do.) If you want an actual string value
126127 <quote>NULL</>, you must put double quotes around it.
@@ -163,6 +164,19 @@ SELECT * FROM sal_emp;
163164</programlisting>
164165 </para>
165166
167+ <para>
168+ Multidimensional arrays must have matching extents for each
169+ dimension. A mismatch causes an error, for example:
170+
171+ <programlisting>
172+ INSERT INTO sal_emp
173+ VALUES ('Bill',
174+ '{10000, 10000, 10000, 10000}',
175+ '{{"meeting", "lunch"}, {"meeting"}}');
176+ ERROR: multidimensional arrays must have array expressions with matching dimensions
177+ </programlisting>
178+ </para>
179+
166180 <para>
167181 The <literal>ARRAY</> constructor syntax can also be used:
168182<programlisting>
@@ -182,19 +196,6 @@ INSERT INTO sal_emp
182196 constructor syntax is discussed in more detail in
183197 <xref linkend="sql-syntax-array-constructors">.
184198 </para>
185-
186- <para>
187- Multidimensional arrays must have matching extents for each
188- dimension. A mismatch causes an error report, for example:
189-
190- <programlisting>
191- INSERT INTO sal_emp
192- VALUES ('Bill',
193- '{10000, 10000, 10000, 10000}',
194- '{{"meeting", "lunch"}, {"meeting"}}');
195- ERROR: multidimensional arrays must have array expressions with matching dimensions
196- </programlisting>
197- </para>
198199 </sect2>
199200
200201 <sect2 id="arrays-accessing">
@@ -207,7 +208,7 @@ ERROR: multidimensional arrays must have array expressions with matching dimens
207208
208209 <para>
209210 Now, we can run some queries on the table.
210- First, we show how to access a single element of an array at a time .
211+ First, we show how to access a single element of an array.
211212 This query retrieves the names of the employees whose pay changed in
212213 the second quarter:
213214
@@ -221,7 +222,7 @@ SELECT name FROM sal_emp WHERE pay_by_quarter[1] <> pay_by_quarter[2];
221222</programlisting>
222223
223224 The array subscript numbers are written within square brackets.
224- By default <productname>PostgreSQL</productname> usesthe
225+ By default <productname>PostgreSQL</productname> usesa
225226 one-based numbering convention for arrays, that is,
226227 an array of <replaceable>n</> elements starts with <literal>array[1]</literal> and
227228 ends with <literal>array[<replaceable>n</>]</literal>.
@@ -257,7 +258,7 @@ SELECT schedule[1:2][1:1] FROM sal_emp WHERE name = 'Bill';
257258(1 row)
258259</programlisting>
259260
260- If any dimension is written as a slice, i.e. contains a colon, then all
261+ If any dimension is written as a slice, i.e., contains a colon, then all
261262 dimensions are treated as slices. Any dimension that has only a single
262263 number (no colon) is treated as being from <literal>1</>
263264 to the number specified. For example, <literal>[2]</> is treated as
@@ -288,13 +289,14 @@ SELECT schedule[1:2][2] FROM sal_emp WHERE name = 'Bill';
288289
289290 <para>
290291 An array slice expression likewise yields null if the array itself or
291- any of the subscript expressions are null. However, in other corner
292+ any of the subscript expressions are null. However, in other
292293 cases such as selecting an array slice that
293294 is completely outside the current array bounds, a slice expression
294295 yields an empty (zero-dimensional) array instead of null. (This
295296 does not match non-slice behavior and is done for historical reasons.)
296297 If the requested slice partially overlaps the array bounds, then it
297- is silently reduced to just the overlapping region.
298+ is silently reduced to just the overlapping region instead of
299+ returning null.
298300 </para>
299301
300302 <para>
@@ -311,7 +313,7 @@ SELECT array_dims(schedule) FROM sal_emp WHERE name = 'Carol';
311313</programlisting>
312314
313315 <function>array_dims</function> produces a <type>text</type> result,
314- which is convenient for people to read but perhapsnot so convenient
316+ which is convenient for people to read but perhapsinconvenient
315317 for programs. Dimensions can also be retrieved with
316318 <function>array_upper</function> and <function>array_lower</function>,
317319 which return the upper and lower bound of a
@@ -380,24 +382,24 @@ UPDATE sal_emp SET pay_by_quarter[1:2] = '{27000,27000}'
380382 </para>
381383
382384 <para>
383- A stored array value can be enlarged by assigning toelement(s) not already
385+ A stored array value can be enlarged by assigning toelements not already
384386 present. Any positions between those previously present and the newly
385- assignedelement(s) will be filled with nulls. For example, if array
387+ assignedelements will be filled with nulls. For example, if array
386388 <literal>myarray</> currently has 4 elements, it will have six
387- elements after an update that assigns to <literal>myarray[6]</>,
388- and <literal>myarray[5]</> will contain a null.
389+ elements after an update that assigns to <literal>myarray[6]</>;
390+ <literal>myarray[5]</> will contain null.
389391 Currently, enlargement in this fashion is only allowed for one-dimensional
390392 arrays, not multidimensional arrays.
391393 </para>
392394
393395 <para>
394396 Subscripted assignment allows creation of arrays that do not use one-based
395397 subscripts. For example one might assign to <literal>myarray[-2:7]</> to
396- create an array with subscript valuesrunning from -2 to 7.
398+ create an array with subscript values from -2 to 7.
397399 </para>
398400
399401 <para>
400- New array values can also be constructedby using the concatenation operator,
402+ New array values can also be constructed using the concatenation operator,
401403 <literal>||</literal>:
402404<programlisting>
403405SELECT ARRAY[1,2] || ARRAY[3,4];
@@ -415,14 +417,14 @@ SELECT ARRAY[5,6] || ARRAY[[1,2],[3,4]];
415417 </para>
416418
417419 <para>
418- The concatenation operator allows a single element to be pushedon to the
420+ The concatenation operator allows a single element to be pushed to the
419421 beginning or end of a one-dimensional array. It also accepts two
420422 <replaceable>N</>-dimensional arrays, or an <replaceable>N</>-dimensional
421423 and an <replaceable>N+1</>-dimensional array.
422424 </para>
423425
424426 <para>
425- When a single element is pushedon to either the beginning or end of a
427+ When a single element is pushed to either the beginning or end of a
426428 one-dimensional array, the result is an array with the same lower bound
427429 subscript as the array operand. For example:
428430<programlisting>
@@ -461,7 +463,7 @@ SELECT array_dims(ARRAY[[1,2],[3,4]] || ARRAY[[5,6],[7,8],[9,0]]);
461463 </para>
462464
463465 <para>
464- When an <replaceable>N</>-dimensional array is pushedon to the beginning
466+ When an <replaceable>N</>-dimensional array is pushed to the beginning
465467 or end of an <replaceable>N+1</>-dimensional array, the result is
466468 analogous to the element-array case above. Each <replaceable>N</>-dimensional
467469 sub-array is essentially an element of the <replaceable>N+1</>-dimensional
@@ -482,7 +484,7 @@ SELECT array_dims(ARRAY[1,2] || ARRAY[[3,4],[5,6]]);
482484 arrays, but <function>array_cat</function> supports multidimensional arrays.
483485
484486 Note that the concatenation operator discussed above is preferred over
485- direct use of these functions. In fact,the functionsexist primarily for use
487+ direct use of these functions. In fact,these functions primarily exist for use
486488 in implementing the concatenation operator. However, they might be directly
487489 useful in the creation of user-defined aggregates. Some examples:
488490
@@ -528,8 +530,8 @@ SELECT array_cat(ARRAY[5,6], ARRAY[[1,2],[3,4]]);
528530 </indexterm>
529531
530532 <para>
531- To search for a value in an array,you must check each valueof the
532- array. This can be doneby hand , if you know the size of the array.
533+ To search for a value in an array, each valuemust be checked.
534+ This can be donemanually , if you know the size of the array.
533535 For example:
534536
535537<programlisting>
@@ -540,15 +542,15 @@ SELECT * FROM sal_emp WHERE pay_by_quarter[1] = 10000 OR
540542</programlisting>
541543
542544 However, this quickly becomes tedious for large arrays, and is not
543- helpful if the size of the array isuncertain . An alternative method is
545+ helpful if the size of the array isunknown . An alternative method is
544546 described in <xref linkend="functions-comparisons">. The above
545547 query could be replaced by:
546548
547549<programlisting>
548550SELECT * FROM sal_emp WHERE 10000 = ANY (pay_by_quarter);
549551</programlisting>
550552
551- In addition, youcould find rows where the arrayhad all values
553+ In addition, youcan find rows where the arrayhas all values
552554 equal to 10000 with:
553555
554556<programlisting>
@@ -578,7 +580,7 @@ SELECT * FROM
578580 can be a sign of database misdesign. Consider
579581 using a separate table with a row for each item that would be an
580582 array element. This will be easier to search, and is likely to
581- scaleup betterto largenumbers of elements.
583+ scale betterfor a largenumber of elements.
582584 </para>
583585 </tip>
584586 </sect2>
@@ -600,9 +602,9 @@ SELECT * FROM
600602 The delimiter character is usually a comma (<literal>,</>) but can be
601603 something else: it is determined by the <literal>typdelim</> setting
602604 for the array's element type. (Among the standard data types provided
603- in the <productname>PostgreSQL</productname> distribution,type
604- <literal>box</> but all the others
605- use comma.) In a multidimensional array, each dimension (row, plane,
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,
606608 cube, etc.) gets its own level of curly braces, and delimiters
607609 must be written between adjacent curly-braced entities of the same level.
608610 </para>
@@ -614,7 +616,7 @@ SELECT * FROM
614616 <literal>NULL</>. Double quotes and backslashes
615617 embedded in element values will be backslash-escaped. For numeric
616618 data types it is safe to assume that double quotes will never appear, but
617- for textual data types one should be prepared to cope with either presence
619+ for textual data types one should be prepared to cope with eitherthe presence
618620 or absence of quotes.
619621 </para>
620622
@@ -647,27 +649,27 @@ SELECT f1[1][-2][3] AS e1, f1[1][-1][5] AS e2
647649 or backslashes disables this and allows the literal string value
648650 <quote>NULL</> to be entered. Also, for backwards compatibility with
649651 pre-8.2 versions of <productname>PostgreSQL</>, the <xref
650- linkend="guc-array-nulls"> configuration parametermight be turned
652+ linkend="guc-array-nulls"> configuration parametercan be turned
651653 <literal>off</> to suppress recognition of <literal>NULL</> as a NULL.
652654 </para>
653655
654656 <para>
655- As shown previously, when writing an array value you canwrite double
657+ As shown previously, when writing an array value you canuse double
656658 quotes around any individual array element. You <emphasis>must</> do so
657659 if the element value would otherwise confuse the array-value parser.
658- For example, elements containing curly braces, commas (orwhatever the
659- delimiter character is ), double quotes, backslashes, or leading or trailing
660+ For example, elements containing curly braces, commas (or the matching
661+ delimiter character), double quotes, backslashes, or leading or trailing
660662 whitespace must be double-quoted. Empty strings and strings matching the
661663 word <literal>NULL</> must be quoted, too. To put a double quote or
662664 backslash in a quoted array element value, use escape string syntax
663- and precede it with a backslash. Alternatively, you can use
665+ and precede it with a backslash. Alternatively, you canavoid quotes and use
664666 backslash-escaping to protect all data characters that would otherwise
665667 be taken as array syntax.
666668 </para>
667669
668670 <para>
669- You canwrite whitespace before a left brace or after a right
670- brace. You can alsowrite whitespace before or after any individual item
671+ You canuse whitespace before a left brace or after a right
672+ brace. You can alsoadd whitespace before or after any individual item
671673 string. In all of these cases the whitespace will be ignored. However,
672674 whitespace within double-quoted elements, or surrounded on both sides by
673675 non-whitespace characters of an element, is not ignored.