From cecb6075594a407b7adcd9c9a0c243ca4b43c9a3 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Thu, 17 Nov 2005 22:14:56 +0000 Subject: Make SQL arrays support null elements. This commit fixes the core array functionality, but I still need to make another pass looking at places that incidentally use arrays (such as ACL manipulation) to make sure they are null-safe. Contrib needs work too. I have not changed the behaviors that are still under discussion about array comparison and what to do with lower bounds. --- doc/src/sgml/array.sgml | 110 +++++++++++++++++++++++++----------------------- 1 file changed, 58 insertions(+), 52 deletions(-) (limited to 'doc/src/sgml/array.sgml') diff --git a/doc/src/sgml/array.sgml b/doc/src/sgml/array.sgml index 2d179fd7f1..c24646e43c 100644 --- a/doc/src/sgml/array.sgml +++ b/doc/src/sgml/array.sgml @@ -1,4 +1,4 @@ - + Arrays @@ -110,6 +110,13 @@ CREATE TABLE tictactoe ( three subarrays of integers. + + To set an element of an array constant to NULL, write NULL + for the element value. (Any upper- or lower-case variant of + NULL will do.) If you want an actual string value + NULL, you must put double quotes around it. + + (These kinds of array constants are actually only a special case of the generic type constants discussed in Now we can show some INSERT statements. - -INSERT INTO sal_emp - VALUES ('Bill', - '{10000, 10000, 10000, 10000}', - '{{"meeting", "lunch"}, {"meeting"}}'); -ERROR: multidimensional arrays must have array expressions with matching dimensions - - - Note that multidimensional arrays must have matching extents for each - dimension. A mismatch causes an error report. - INSERT INTO sal_emp VALUES ('Bill', @@ -145,15 +141,9 @@ INSERT INTO sal_emp - - A limitation of the present array implementation is that individual - elements of an array cannot be SQL null values. The entire array - can be set to null, but you can't have an array with some elements - null and some not. (This is likely to change in the future.) - - The result of the previous two inserts looks like this: + SELECT * FROM sal_emp; name | pay_by_quarter | schedule @@ -183,6 +173,19 @@ INSERT INTO sal_emp constructor syntax is discussed in more detail in . + + + Multidimensional arrays must have matching extents for each + dimension. A mismatch causes an error report, for example: + + +INSERT INTO sal_emp + VALUES ('Bill', + '{10000, 10000, 10000, 10000}', + '{{"meeting", "lunch"}, {"meeting"}}'); +ERROR: multidimensional arrays must have array expressions with matching dimensions + + @@ -262,14 +265,22 @@ SELECT schedule[1:2][2] FROM sal_emp WHERE name = 'Bill'; - Fetching from outside the current bounds of an array yields a - SQL null value, not an error. For example, if schedule + An array subscript expression will return null if either the array itself or + any of the subscript expressions are null. Also, null is returned if a + subscript is outside the array bounds (this case does not raise an error). + For example, if schedule currently has the dimensions [1:3][1:2] then referencing schedule[3][3] yields NULL. Similarly, an array reference with the wrong number of subscripts yields a null rather than an error. - Fetching an array slice that - is completely outside the current bounds likewise yields a null array; - but if the requested slice partially overlaps the array bounds, then it + + + + An array slice expression likewise yields null if the array itself or + any of the subscript expressions are null. However, in other corner + cases such as selecting an array slice that + is completely outside the current array bounds, a slice expression + yields an empty (zero-dimensional) array instead of null. + If the requested slice partially overlaps the array bounds, then it is silently reduced to just the overlapping region. @@ -349,7 +360,7 @@ UPDATE sal_emp SET pay_by_quarter[1:2] = '{27000,27000}' - Array slice assignment allows creation of arrays that do not use one-based + Subscripted assignment allows creation of arrays that do not use one-based subscripts. For example one might assign to myarray[-2:7] to create an array with subscript values running from -2 to 7. @@ -442,7 +453,7 @@ SELECT array_dims(ARRAY[1,2] || ARRAY[[3,4],[5,6]]); arrays, but array_cat supports multidimensional arrays. Note that the concatenation operator discussed above is preferred over - direct use of these functions. In fact, the functions are primarily for use + direct use of these functions. In fact, the functions exist primarily for use in implementing the concatenation operator. However, they may be directly useful in the creation of user-defined aggregates. Some examples: @@ -544,8 +555,9 @@ SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter); The array output routine will put double quotes around element values - if they are empty strings or contain curly braces, delimiter characters, - double quotes, backslashes, or white space. Double quotes and backslashes + if they are empty strings, contain curly braces, delimiter characters, + double quotes, backslashes, or white space, or match the word + NULL. Double quotes and backslashes embedded in element values will be backslash-escaped. For numeric data types it is safe to assume that double quotes will never appear, but for textual data types one should be prepared to cope with either presence @@ -555,35 +567,15 @@ SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter); By default, the lower bound index value of an array's dimensions is - set to one. If any of an array's dimensions has a lower bound index not - equal to one, an additional decoration that indicates the actual - array dimensions will precede the array structure decoration. + set to one. To represent arrays with other lower bounds, the array + subscript ranges can be specified explicitly before writing the + array contents. This decoration consists of square brackets ([]) around each array dimension's lower and upper bounds, with a colon (:) delimiter character in between. The array dimension decoration is followed by an equal sign (=). For example: -SELECT 1 || ARRAY[2,3] AS array; - - array ---------------- - [0:2]={1,2,3} -(1 row) - -SELECT ARRAY[1,2] || ARRAY[[3,4]] AS array; - - array --------------------------- - [0:1][1:2]={{1,2},{3,4}} -(1 row) - - - - - This syntax can also be used to specify non-default array subscripts - in an array literal. For example: - SELECT f1[1][-2][3] AS e1, f1[1][-1][5] AS e2 FROM (SELECT '[1:1][-2:-1][3:5]={{{1,2,3},{4,5,6}}}'::int[] AS f1) AS ss; @@ -592,6 +584,18 @@ SELECT f1[1][-2][3] AS e1, f1[1][-1][5] AS e2 1 | 6 (1 row) + The array output routine will include explicit dimensions in its result + only when there are one or more lower bounds different from one. + + + + If the value written for an element is NULL (in any case + variant), the element is taken to be NULL. The presence of any quotes + or backslashes disables this and allows the literal string value + NULL to be entered. Also, for backwards compatibility with + pre-8.2 versions of PostgreSQL, the configuration parameter may be turned + off to suppress recognition of NULL as a NULL. @@ -600,7 +604,9 @@ SELECT f1[1][-2][3] AS e1, f1[1][-1][5] AS e2 if the element value would otherwise confuse the array-value parser. For example, elements containing curly braces, commas (or whatever the delimiter character is), double quotes, backslashes, or leading or trailing - whitespace must be double-quoted. To put a double quote or backslash in a + whitespace must be double-quoted. Empty strings and strings matching the + word NULL must be quoted, too. To put a double quote or + backslash in a quoted array element value, precede it with a backslash. Alternatively, you can use backslash-escaping to protect all data characters that would otherwise be taken as array syntax. -- cgit v1.2.1