{"Entry":{"collection":"type","key":"arrays","name":"Arrays","aliases":[],"metadata":{"aliases":[],"category":"Type families","content_hash":"6278708e61b61149754ac07035fee4895f72ce0069b2f742679ff82819871b1f","imported_at":"2026-09-30T00:40:35.427614+08:00","name":"Arrays","name_zh":"","slug":"arrays","summary":"PostgreSQL allows columns of a table to be defined as variable-length multidimensional arrays. Arrays of any built-in or user-defined base type, enum type, or composite type can be created. Arrays of domains are not yet supported."}},"Definition":{"Collection":"type","Key":"arrays","SourceDatabase":"center","Version":"18","SourceTable":"data_type","SourceKey":"arrays","SourceRevision":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","Facts":{"aliases":[],"casts":[],"catalog":{},"coverage":"documented type family or SQL syntax; not a catalog object","description":["PostgreSQL allows columns of a table to be defined as variable-length multidimensional arrays. Arrays of any built-in or user-defined base type, enum type, composite type, range type, or domain can be created."],"facts":[{"label":"Object boundary","value":"User-defined type family; not a finite list of user objects"}],"manual_html":"\u003cdiv class=\"sect1\" id=\"ARRAYS\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch2 class=\"title\"\u003e8.15. Arrays \u003c/h2\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\n\u003cp\u003e\u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e allows columns of a table to be defined as variable-length multidimensional arrays. Arrays of any built-in or user-defined base type, enum type, composite type, range type, or domain can be created.\u003c/p\u003e\n\u003cdiv class=\"sect2\" id=\"ARRAYS-DECLARATION\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3 class=\"title\"\u003e8.15.1. Declaration of Array Types \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eTo illustrate the use of array types, we create this table:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eCREATE TABLE sal_emp (\n    name            text,\n    pay_by_quarter  integer[],\n    schedule        text[][]\n);\n\u003c/pre\u003e\n\u003cp\u003eAs shown, an array data type is named by appending square brackets (\u003ccode class=\"literal\"\u003e[]\u003c/code\u003e) to the data type name of the array elements. The above command will create a table named \u003ccode class=\"structname\"\u003esal_emp\u003c/code\u003e with a column of type \u003ccode class=\"type\"\u003etext\u003c/code\u003e (\u003ccode class=\"structfield\"\u003ename\u003c/code\u003e), a one-dimensional array of type \u003ccode class=\"type\"\u003einteger\u003c/code\u003e (\u003ccode class=\"structfield\"\u003epay_by_quarter\u003c/code\u003e), which represents the employee's salary by quarter, and a two-dimensional array of \u003ccode class=\"type\"\u003etext\u003c/code\u003e (\u003ccode class=\"structfield\"\u003eschedule\u003c/code\u003e), which represents the employee's weekly schedule.\u003c/p\u003e\n\u003cp\u003eThe syntax for \u003ccode class=\"command\"\u003eCREATE TABLE\u003c/code\u003e allows the exact size of arrays to be specified, for example:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eCREATE TABLE tictactoe (\n    squares   integer[3][3]\n);\n\u003c/pre\u003e\n\u003cp\u003eHowever, the current implementation ignores any supplied array size limits, i.e., the behavior is the same as for arrays of unspecified length.\u003c/p\u003e\n\u003cp\u003eThe current implementation does not enforce the declared number of dimensions either. Arrays of a particular element type are all considered to be of the same type, regardless of size or number of dimensions. So, declaring the array size or number of dimensions in \u003ccode class=\"command\"\u003eCREATE TABLE\u003c/code\u003e is simply documentation; it does not affect run-time behavior.\u003c/p\u003e\n\u003cp\u003eAn alternative syntax, which conforms to the SQL standard by using the keyword \u003ccode class=\"literal\"\u003eARRAY\u003c/code\u003e, can be used for one-dimensional arrays. \u003ccode class=\"structfield\"\u003epay_by_quarter\u003c/code\u003e could have been defined as:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003e    pay_by_quarter  integer ARRAY[4],\n\u003c/pre\u003e\n\u003cp\u003eOr, if no array size is to be specified:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003e    pay_by_quarter  integer ARRAY,\n\u003c/pre\u003e\n\u003cp\u003eAs before, however, \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e does not enforce the size restriction in any case.\u003c/p\u003e\n\u003c/div\u003e\n\u003cdiv class=\"sect2\" id=\"ARRAYS-INPUT\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3 class=\"title\"\u003e8.15.2. Array Value Input \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eTo write an array value as a literal constant, enclose the element values within curly braces and separate them by commas. (If you know C, this is not unlike the C syntax for initializing structures.) You can put double quotes around any element value, and must do so if it contains commas or curly braces. (More details appear below.) Thus, the general format of an array constant is the following:\u003c/p\u003e\n\u003cpre class=\"synopsis\"\u003e'{ \u003cem class=\"replaceable\"\u003e\u003ccode\u003eval1\u003c/code\u003e\u003c/em\u003e \u003cem class=\"replaceable\"\u003e\u003ccode\u003edelim\u003c/code\u003e\u003c/em\u003e \u003cem class=\"replaceable\"\u003e\u003ccode\u003eval2\u003c/code\u003e\u003c/em\u003e \u003cem class=\"replaceable\"\u003e\u003ccode\u003edelim\u003c/code\u003e\u003c/em\u003e ... }'\n\u003c/pre\u003e\n\u003cp\u003ewhere \u003cem class=\"replaceable\"\u003e\u003ccode\u003edelim\u003c/code\u003e\u003c/em\u003e is the delimiter character for the type, as recorded in its \u003ccode class=\"literal\"\u003epg_type\u003c/code\u003e entry. Among the standard data types provided in the \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e distribution, all use a comma (\u003ccode class=\"literal\"\u003e,\u003c/code\u003e), except for type \u003ccode class=\"type\"\u003ebox\u003c/code\u003e which uses a semicolon (\u003ccode class=\"literal\"\u003e;\u003c/code\u003e). Each \u003cem class=\"replaceable\"\u003e\u003ccode\u003eval\u003c/code\u003e\u003c/em\u003e is either a constant of the array element type, or a subarray. An example of an array constant is:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003e'{{1,2,3},{4,5,6},{7,8,9}}'\n\u003c/pre\u003e\n\u003cp\u003eThis constant is a two-dimensional, 3-by-3 array consisting of three subarrays of integers.\u003c/p\u003e\n\u003cp\u003eTo set an element of an array constant to NULL, write \u003ccode class=\"literal\"\u003eNULL\u003c/code\u003e for the element value. (Any upper- or lower-case variant of \u003ccode class=\"literal\"\u003eNULL\u003c/code\u003e will do.) If you want an actual string value \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003eNULL\u003c/span\u003e”\u003c/span\u003e, you must put double quotes around it.\u003c/p\u003e\n\u003cp\u003e(These kinds of array constants are actually only a special case of the generic type constants discussed in \u003ca class=\"xref\" href=\"/docs/18/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS-GENERIC\" title=\"4.1.2.7. Constants of Other Types\"\u003eSection 4.1.2.7\u003c/a\u003e. The constant is initially treated as a string and passed to the array input conversion routine. An explicit type specification might be necessary.)\u003c/p\u003e\n\u003cp\u003eNow we can show some \u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e statements:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eINSERT INTO sal_emp\n    VALUES ('Bill',\n    '{10000, 10000, 10000, 10000}',\n    '{{\"meeting\", \"lunch\"}, {\"training\", \"presentation\"}}');\n\nINSERT INTO sal_emp\n    VALUES ('Carol',\n    '{20000, 25000, 25000, 25000}',\n    '{{\"breakfast\", \"consulting\"}, {\"meeting\", \"lunch\"}}');\n\u003c/pre\u003e\n\u003cp\u003eThe result of the previous two inserts looks like this:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT * FROM sal_emp;\n name  |      pay_by_quarter       |                 schedule\n-------+---------------------------+-------------------------------------------\n Bill  | {10000,10000,10000,10000} | {{meeting,lunch},{training,presentation}}\n Carol | {20000,25000,25000,25000} | {{breakfast,consulting},{meeting,lunch}}\n(2 rows)\n\u003c/pre\u003e\n\u003cp\u003eMultidimensional arrays must have matching extents for each dimension. A mismatch causes an error, for example:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eINSERT INTO sal_emp\n    VALUES ('Bill',\n    '{10000, 10000, 10000, 10000}',\n    '{{\"meeting\", \"lunch\"}, {\"meeting\"}}');\nERROR:  malformed array literal: \"{{\"meeting\", \"lunch\"}, {\"meeting\"}}\"\nDETAIL:  Multidimensional arrays must have sub-arrays with matching dimensions.\n\u003c/pre\u003e\n\u003cp\u003eThe \u003ccode class=\"literal\"\u003eARRAY\u003c/code\u003e constructor syntax can also be used:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eINSERT INTO sal_emp\n    VALUES ('Bill',\n    ARRAY[10000, 10000, 10000, 10000],\n    ARRAY[['meeting', 'lunch'], ['training', 'presentation']]);\n\nINSERT INTO sal_emp\n    VALUES ('Carol',\n    ARRAY[20000, 25000, 25000, 25000],\n    ARRAY[['breakfast', 'consulting'], ['meeting', 'lunch']]);\n\u003c/pre\u003e\n\u003cp\u003eNotice that the array elements are ordinary SQL constants or expressions; for instance, string literals are single quoted, instead of double quoted as they would be in an array literal. The \u003ccode class=\"literal\"\u003eARRAY\u003c/code\u003e constructor syntax is discussed in more detail in \u003ca class=\"xref\" href=\"/docs/18/sql-expressions.html#SQL-SYNTAX-ARRAY-CONSTRUCTORS\" title=\"4.2.12. Array Constructors\"\u003eSection 4.2.12\u003c/a\u003e.\u003c/p\u003e\n\u003c/div\u003e\n\u003cdiv class=\"sect2\" id=\"ARRAYS-ACCESSING\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3 class=\"title\"\u003e8.15.3. Accessing Arrays \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eNow, we can run some queries on the table. First, we show how to access a single element of an array. This query retrieves the names of the employees whose pay changed in the second quarter:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT name FROM sal_emp WHERE pay_by_quarter[1] \u0026lt;\u0026gt; pay_by_quarter[2];\n\n name\n-------\n Carol\n(1 row)\n\u003c/pre\u003e\n\u003cp\u003eThe array subscript numbers are written within square brackets. By default \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e uses a one-based numbering convention for arrays, that is, an array of \u003cem class=\"replaceable\"\u003e\u003ccode\u003en\u003c/code\u003e\u003c/em\u003e elements starts with \u003ccode class=\"literal\"\u003earray[1]\u003c/code\u003e and ends with \u003ccode class=\"literal\"\u003earray[\u003cem class=\"replaceable\"\u003e\u003ccode\u003en\u003c/code\u003e\u003c/em\u003e]\u003c/code\u003e.\u003c/p\u003e\n\u003cp\u003eThis query retrieves the third quarter pay of all employees:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT pay_by_quarter[3] FROM sal_emp;\n\n pay_by_quarter\n----------------\n          10000\n          25000\n(2 rows)\n\u003c/pre\u003e\n\u003cp\u003eWe can also access arbitrary rectangular slices of an array, or subarrays. An array slice is denoted by writing \u003ccode class=\"literal\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003elower-bound\u003c/code\u003e\u003c/em\u003e:\u003cem class=\"replaceable\"\u003e\u003ccode\u003eupper-bound\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e for one or more array dimensions. For example, this query retrieves the first item on Bill's schedule for the first two days of the week:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT schedule[1:2][1:1] FROM sal_emp WHERE name = 'Bill';\n\n        schedule\n------------------------\n {{meeting},{training}}\n(1 row)\n\u003c/pre\u003e\n\u003cp\u003eIf any dimension is written as a slice, i.e., contains a colon, then all dimensions are treated as slices. Any dimension that has only a single number (no colon) is treated as being from 1 to the number specified. For example, \u003ccode class=\"literal\"\u003e[2]\u003c/code\u003e is treated as \u003ccode class=\"literal\"\u003e[1:2]\u003c/code\u003e, as in this example:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT schedule[1:2][2] FROM sal_emp WHERE name = 'Bill';\n\n                 schedule\n-------------------------------------------\n {{meeting,lunch},{training,presentation}}\n(1 row)\n\u003c/pre\u003e\n\u003cp\u003eTo avoid confusion with the non-slice case, it's best to use slice syntax for all dimensions, e.g., \u003ccode class=\"literal\"\u003e[1:2][1:1]\u003c/code\u003e, not \u003ccode class=\"literal\"\u003e[2][1:1]\u003c/code\u003e.\u003c/p\u003e\n\u003cp\u003eIt is possible to omit the \u003cem class=\"replaceable\"\u003e\u003ccode\u003elower-bound\u003c/code\u003e\u003c/em\u003e and/or \u003cem class=\"replaceable\"\u003e\u003ccode\u003eupper-bound\u003c/code\u003e\u003c/em\u003e of a slice specifier; the missing bound is replaced by the lower or upper limit of the array's subscripts. For example:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT schedule[:2][2:] FROM sal_emp WHERE name = 'Bill';\n\n        schedule\n------------------------\n {{lunch},{presentation}}\n(1 row)\n\nSELECT schedule[:][1:1] FROM sal_emp WHERE name = 'Bill';\n\n        schedule\n------------------------\n {{meeting},{training}}\n(1 row)\n\u003c/pre\u003e\n\u003cp\u003eAn 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 \u003ccode class=\"literal\"\u003eschedule\u003c/code\u003e currently has the dimensions \u003ccode class=\"literal\"\u003e[1:3][1:2]\u003c/code\u003e then referencing \u003ccode class=\"literal\"\u003eschedule[3][3]\u003c/code\u003e yields NULL. Similarly, an array reference with the wrong number of subscripts yields a null rather than an error.\u003c/p\u003e\n\u003cp\u003eAn array slice expression likewise yields null if the array itself or any of the subscript expressions are null. However, in other 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. (This does not match non-slice behavior and is done for historical reasons.) If the requested slice partially overlaps the array bounds, then it is silently reduced to just the overlapping region instead of returning null.\u003c/p\u003e\n\u003cp\u003eThe current dimensions of any array value can be retrieved with the \u003ccode class=\"function\"\u003earray_dims\u003c/code\u003e function:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT array_dims(schedule) FROM sal_emp WHERE name = 'Carol';\n\n array_dims\n------------\n [1:2][1:2]\n(1 row)\n\u003c/pre\u003e\n\u003cp\u003e\u003ccode class=\"function\"\u003earray_dims\u003c/code\u003e produces a \u003ccode class=\"type\"\u003etext\u003c/code\u003e result, which is convenient for people to read but perhaps inconvenient for programs. Dimensions can also be retrieved with \u003ccode class=\"function\"\u003earray_upper\u003c/code\u003e and \u003ccode class=\"function\"\u003earray_lower\u003c/code\u003e, which return the upper and lower bound of a specified array dimension, respectively:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT array_upper(schedule, 1) FROM sal_emp WHERE name = 'Carol';\n\n array_upper\n-------------\n           2\n(1 row)\n\u003c/pre\u003e\n\u003cp\u003e\u003ccode class=\"function\"\u003earray_length\u003c/code\u003e will return the length of a specified array dimension:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT array_length(schedule, 1) FROM sal_emp WHERE name = 'Carol';\n\n array_length\n--------------\n            2\n(1 row)\n\u003c/pre\u003e\n\u003cp\u003e\u003ccode class=\"function\"\u003ecardinality\u003c/code\u003e returns the total number of elements in an array across all dimensions. It is effectively the number of rows a call to \u003ccode class=\"function\"\u003eunnest\u003c/code\u003e would yield:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT cardinality(schedule) FROM sal_emp WHERE name = 'Carol';\n\n cardinality\n-------------\n           4\n(1 row)\n\u003c/pre\u003e\n\u003c/div\u003e\n\u003cdiv class=\"sect2\" id=\"ARRAYS-MODIFYING\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3 class=\"title\"\u003e8.15.4. Modifying Arrays \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eAn array value can be replaced completely:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eUPDATE sal_emp SET pay_by_quarter = '{25000,25000,27000,27000}'\n    WHERE name = 'Carol';\n\u003c/pre\u003e\n\u003cp\u003eor using the \u003ccode class=\"literal\"\u003eARRAY\u003c/code\u003e expression syntax:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eUPDATE sal_emp SET pay_by_quarter = ARRAY[25000,25000,27000,27000]\n    WHERE name = 'Carol';\n\u003c/pre\u003e\n\u003cp\u003eAn array can also be updated at a single element:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eUPDATE sal_emp SET pay_by_quarter[4] = 15000\n    WHERE name = 'Bill';\n\u003c/pre\u003e\n\u003cp\u003eor updated in a slice:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eUPDATE sal_emp SET pay_by_quarter[1:2] = '{27000,27000}'\n    WHERE name = 'Carol';\n\u003c/pre\u003e\n\u003cp\u003eThe slice syntaxes with omitted \u003cem class=\"replaceable\"\u003e\u003ccode\u003elower-bound\u003c/code\u003e\u003c/em\u003e and/or \u003cem class=\"replaceable\"\u003e\u003ccode\u003eupper-bound\u003c/code\u003e\u003c/em\u003e can be used too, but only when updating an array value that is not NULL or zero-dimensional (otherwise, there is no existing subscript limit to substitute).\u003c/p\u003e\n\u003cp\u003eA stored array value can be enlarged by assigning to elements not already present. Any positions between those previously present and the newly assigned elements will be filled with nulls. For example, if array \u003ccode class=\"literal\"\u003emyarray\u003c/code\u003e currently has 4 elements, it will have six elements after an update that assigns to \u003ccode class=\"literal\"\u003emyarray[6]\u003c/code\u003e; \u003ccode class=\"literal\"\u003emyarray[5]\u003c/code\u003e will contain null. Currently, enlargement in this fashion is only allowed for one-dimensional arrays, not multidimensional arrays.\u003c/p\u003e\n\u003cp\u003eSubscripted assignment allows creation of arrays that do not use one-based subscripts. For example one might assign to \u003ccode class=\"literal\"\u003emyarray[-2:7]\u003c/code\u003e to create an array with subscript values from -2 to 7.\u003c/p\u003e\n\u003cp\u003eNew array values can also be constructed using the concatenation operator, \u003ccode class=\"literal\"\u003e||\u003c/code\u003e:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT ARRAY[1,2] || ARRAY[3,4];\n ?column?\n-----------\n {1,2,3,4}\n(1 row)\n\nSELECT ARRAY[5,6] || ARRAY[[1,2],[3,4]];\n      ?column?\n---------------------\n {{5,6},{1,2},{3,4}}\n(1 row)\n\u003c/pre\u003e\n\u003cp\u003eThe concatenation operator allows a single element to be pushed onto the beginning or end of a one-dimensional array. It also accepts two \u003cem class=\"replaceable\"\u003e\u003ccode\u003eN\u003c/code\u003e\u003c/em\u003e-dimensional arrays, or an \u003cem class=\"replaceable\"\u003e\u003ccode\u003eN\u003c/code\u003e\u003c/em\u003e-dimensional and an \u003cem class=\"replaceable\"\u003e\u003ccode\u003eN+1\u003c/code\u003e\u003c/em\u003e-dimensional array.\u003c/p\u003e\n\u003cp\u003eWhen a single element is pushed onto either the beginning or end of a one-dimensional array, the result is an array with the same lower bound subscript as the array operand. For example:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT array_dims(1 || '[0:1]={2,3}'::int[]);\n array_dims\n------------\n [0:2]\n(1 row)\n\nSELECT array_dims(ARRAY[1,2] || 3);\n array_dims\n------------\n [1:3]\n(1 row)\n\u003c/pre\u003e\n\u003cp\u003eWhen two arrays with an equal number of dimensions are concatenated, the result retains the lower bound subscript of the left-hand operand's outer dimension. The result is an array comprising every element of the left-hand operand followed by every element of the right-hand operand. For example:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT array_dims(ARRAY[1,2] || ARRAY[3,4,5]);\n array_dims\n------------\n [1:5]\n(1 row)\n\nSELECT array_dims(ARRAY[[1,2],[3,4]] || ARRAY[[5,6],[7,8],[9,0]]);\n array_dims\n------------\n [1:5][1:2]\n(1 row)\n\u003c/pre\u003e\n\u003cp\u003eWhen an \u003cem class=\"replaceable\"\u003e\u003ccode\u003eN\u003c/code\u003e\u003c/em\u003e-dimensional array is pushed onto the beginning or end of an \u003cem class=\"replaceable\"\u003e\u003ccode\u003eN+1\u003c/code\u003e\u003c/em\u003e-dimensional array, the result is analogous to the element-array case above. Each \u003cem class=\"replaceable\"\u003e\u003ccode\u003eN\u003c/code\u003e\u003c/em\u003e-dimensional sub-array is essentially an element of the \u003cem class=\"replaceable\"\u003e\u003ccode\u003eN+1\u003c/code\u003e\u003c/em\u003e-dimensional array's outer dimension. For example:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT array_dims(ARRAY[1,2] || ARRAY[[3,4],[5,6]]);\n array_dims\n------------\n [1:3][1:2]\n(1 row)\n\u003c/pre\u003e\n\u003cp\u003eAn array can also be constructed by using the functions \u003ccode class=\"function\"\u003earray_prepend\u003c/code\u003e, \u003ccode class=\"function\"\u003earray_append\u003c/code\u003e, or \u003ccode class=\"function\"\u003earray_cat\u003c/code\u003e. The first two only support one-dimensional arrays, but \u003ccode class=\"function\"\u003earray_cat\u003c/code\u003e supports multidimensional arrays. Some examples:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT array_prepend(1, ARRAY[2,3]);\n array_prepend\n---------------\n {1,2,3}\n(1 row)\n\nSELECT array_append(ARRAY[1,2], 3);\n array_append\n--------------\n {1,2,3}\n(1 row)\n\nSELECT array_cat(ARRAY[1,2], ARRAY[3,4]);\n array_cat\n-----------\n {1,2,3,4}\n(1 row)\n\nSELECT array_cat(ARRAY[[1,2],[3,4]], ARRAY[5,6]);\n      array_cat\n---------------------\n {{1,2},{3,4},{5,6}}\n(1 row)\n\nSELECT array_cat(ARRAY[5,6], ARRAY[[1,2],[3,4]]);\n      array_cat\n---------------------\n {{5,6},{1,2},{3,4}}\n\u003c/pre\u003e\n\u003cp\u003eIn simple cases, the concatenation operator discussed above is preferred over direct use of these functions. However, because the concatenation operator is overloaded to serve all three cases, there are situations where use of one of the functions is helpful to avoid ambiguity. For example consider:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT ARRAY[1, 2] || '{3, 4}';  -- the untyped literal is taken as an array\n ?column?\n-----------\n {1,2,3,4}\n\nSELECT ARRAY[1, 2] || '7';                 -- so is this one\nERROR:  malformed array literal: \"7\"\n\nSELECT ARRAY[1, 2] || NULL;                -- so is an undecorated NULL\n ?column?\n----------\n {1,2}\n(1 row)\n\nSELECT array_append(ARRAY[1, 2], NULL);    -- this might have been meant\n array_append\n--------------\n {1,2,NULL}\n\u003c/pre\u003e\n\u003cp\u003eIn the examples above, the parser sees an integer array on one side of the concatenation operator, and a constant of undetermined type on the other. The heuristic it uses to resolve the constant's type is to assume it's of the same type as the operator's other input — in this case, integer array. So the concatenation operator is presumed to represent \u003ccode class=\"function\"\u003earray_cat\u003c/code\u003e, not \u003ccode class=\"function\"\u003earray_append\u003c/code\u003e. When that's the wrong choice, it could be fixed by casting the constant to the array's element type; but explicit use of \u003ccode class=\"function\"\u003earray_append\u003c/code\u003e might be a preferable solution.\u003c/p\u003e\n\u003c/div\u003e\n\u003cdiv class=\"sect2\" id=\"ARRAYS-SEARCHING\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3 class=\"title\"\u003e8.15.5. Searching in Arrays \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eTo search for a value in an array, each value must be checked. This can be done manually, if you know the size of the array. For example:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT * FROM sal_emp WHERE pay_by_quarter[1] = 10000 OR\n                            pay_by_quarter[2] = 10000 OR\n                            pay_by_quarter[3] = 10000 OR\n                            pay_by_quarter[4] = 10000;\n\u003c/pre\u003e\n\u003cp\u003eHowever, this quickly becomes tedious for large arrays, and is not helpful if the size of the array is unknown. An alternative method is described in \u003ca class=\"xref\" href=\"/docs/18/functions-comparisons.html\" title=\"9.25. Row and Array Comparisons\"\u003eSection 9.25\u003c/a\u003e. The above query could be replaced by:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT * FROM sal_emp WHERE 10000 = ANY (pay_by_quarter);\n\u003c/pre\u003e\n\u003cp\u003eIn addition, you can find rows where the array has all values equal to 10000 with:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter);\n\u003c/pre\u003e\n\u003cp\u003eAlternatively, the \u003ccode class=\"function\"\u003egenerate_subscripts\u003c/code\u003e function can be used. For example:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT * FROM\n   (SELECT pay_by_quarter,\n           generate_subscripts(pay_by_quarter, 1) AS s\n      FROM sal_emp) AS foo\n WHERE pay_by_quarter[s] = 10000;\n\u003c/pre\u003e\n\u003cp\u003eThis function is described in \u003ca class=\"xref\" href=\"/docs/18/functions-srf.html#FUNCTIONS-SRF-SUBSCRIPTS\" title=\"Table 9.70. Subscript Generating Functions\"\u003eTable 9.70\u003c/a\u003e.\u003c/p\u003e\n\u003cp\u003eYou can also search an array using the \u003ccode class=\"literal\"\u003e\u0026amp;\u0026amp;\u003c/code\u003e operator, which checks whether the left operand overlaps with the right operand. For instance:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT * FROM sal_emp WHERE pay_by_quarter \u0026amp;\u0026amp; ARRAY[10000];\n\u003c/pre\u003e\n\u003cp\u003eThis and other array operators are further described in \u003ca class=\"xref\" href=\"/docs/18/functions-array.html\" title=\"9.19. Array Functions and Operators\"\u003eSection 9.19\u003c/a\u003e. It can be accelerated by an appropriate index, as described in \u003ca class=\"xref\" href=\"/docs/18/indexes-types.html\" title=\"11.2. Index Types\"\u003eSection 11.2\u003c/a\u003e.\u003c/p\u003e\n\u003cp\u003eYou can also search for specific values in an array using the \u003ccode class=\"function\"\u003earray_position\u003c/code\u003e and \u003ccode class=\"function\"\u003earray_positions\u003c/code\u003e functions. The former returns the subscript of the first occurrence of a value in an array; the latter returns an array with the subscripts of all occurrences of the value in the array. For example:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT array_position(ARRAY['sun','mon','tue','wed','thu','fri','sat'], 'mon');\n array_position\n----------------\n              2\n(1 row)\n\nSELECT array_positions(ARRAY[1, 4, 3, 1, 3, 4, 2, 1], 1);\n array_positions\n-----------------\n {1,4,8}\n(1 row)\n\u003c/pre\u003e\n\u003cdiv class=\"tip\"\u003e\n\u003ch3 class=\"title\"\u003eTip\u003c/h3\u003e\n\u003cp\u003eArrays are not sets; searching for specific array elements can be a sign of database misdesign. Consider using a separate table with a row for each item that would be an array element. This will be easier to search, and is likely to scale better for a large number of elements.\u003c/p\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cdiv class=\"sect2\" id=\"ARRAYS-IO\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3 class=\"title\"\u003e8.15.6. Array Input and Output Syntax \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eThe external text representation of an array value consists of items that are interpreted according to the I/O conversion rules for the array's element type, plus decoration that indicates the array structure. The decoration consists of curly braces (\u003ccode class=\"literal\"\u003e{\u003c/code\u003e and \u003ccode class=\"literal\"\u003e}\u003c/code\u003e) around the array value plus delimiter characters between adjacent items. The delimiter character is usually a comma (\u003ccode class=\"literal\"\u003e,\u003c/code\u003e) but can be something else: it is determined by the \u003ccode class=\"literal\"\u003etypdelim\u003c/code\u003e setting for the array's element type. Among the standard data types provided in the \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e distribution, all use a comma, except for type \u003ccode class=\"type\"\u003ebox\u003c/code\u003e, which uses a semicolon (\u003ccode class=\"literal\"\u003e;\u003c/code\u003e). In a multidimensional array, each dimension (row, plane, cube, etc.) gets its own level of curly braces, and delimiters must be written between adjacent curly-braced entities of the same level.\u003c/p\u003e\n\u003cp\u003eThe array output routine will put double quotes around element values if they are empty strings, contain curly braces, delimiter characters, double quotes, backslashes, or white space, or match the word \u003ccode class=\"literal\"\u003eNULL\u003c/code\u003e. 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 the presence or absence of quotes.\u003c/p\u003e\n\u003cp\u003eBy default, the lower bound index value of an array's dimensions is 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 (\u003ccode class=\"literal\"\u003e[]\u003c/code\u003e) around each array dimension's lower and upper bounds, with a colon (\u003ccode class=\"literal\"\u003e:\u003c/code\u003e) delimiter character in between. The array dimension decoration is followed by an equal sign (\u003ccode class=\"literal\"\u003e=\u003c/code\u003e). For example:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT f1[1][-2][3] AS e1, f1[1][-1][5] AS e2\n FROM (SELECT '[1:1][-2:-1][3:5]={{{1,2,3},{4,5,6}}}'::int[] AS f1) AS ss;\n\n e1 | e2\n----+----\n  1 |  6\n(1 row)\n\u003c/pre\u003e\n\u003cp\u003eThe array output routine will include explicit dimensions in its result only when there are one or more lower bounds different from one.\u003c/p\u003e\n\u003cp\u003eIf the value written for an element is \u003ccode class=\"literal\"\u003eNULL\u003c/code\u003e (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 \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003eNULL\u003c/span\u003e”\u003c/span\u003e to be entered. Also, for backward compatibility with pre-8.2 versions of \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e, the \u003ca class=\"xref\" href=\"/docs/18/runtime-config-compatible.html#GUC-ARRAY-NULLS\"\u003earray_nulls\u003c/a\u003e configuration parameter can be turned \u003ccode class=\"literal\"\u003eoff\u003c/code\u003e to suppress recognition of \u003ccode class=\"literal\"\u003eNULL\u003c/code\u003e as a NULL.\u003c/p\u003e\n\u003cp\u003eAs shown previously, when writing an array value you can use double quotes around any individual array element. You \u003cspan class=\"emphasis\"\u003e\u003cem\u003emust\u003c/em\u003e\u003c/span\u003e do so if the element value would otherwise confuse the array-value parser. For example, elements containing curly braces, commas (or the data type's delimiter character), double quotes, backslashes, or leading or trailing whitespace must be double-quoted. Empty strings and strings matching the word \u003ccode class=\"literal\"\u003eNULL\u003c/code\u003e 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 avoid quotes and use backslash-escaping to protect all data characters that would otherwise be taken as array syntax.\u003c/p\u003e\n\u003cp\u003eYou can add whitespace before a left brace or after a right brace. You can also add whitespace before or after any individual item string. In all of these cases the whitespace will be ignored. However, whitespace within double-quoted elements, or surrounded on both sides by non-whitespace characters of an element, is not ignored.\u003c/p\u003e\n\u003cdiv class=\"tip\"\u003e\n\u003ch3 class=\"title\"\u003eTip\u003c/h3\u003e\n\u003cp\u003eThe \u003ccode class=\"literal\"\u003eARRAY\u003c/code\u003e constructor syntax (see \u003ca class=\"xref\" href=\"/docs/18/sql-expressions.html#SQL-SYNTAX-ARRAY-CONSTRUCTORS\" title=\"4.2.12. Array Constructors\"\u003eSection 4.2.12\u003c/a\u003e) is often easier to work with than the array-literal syntax when writing array values in SQL commands. In \u003ccode class=\"literal\"\u003eARRAY\u003c/code\u003e, individual element values are written the same way they would be written when not members of an array.\u003c/p\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e","manual_path":"arrays.html","operator_classes":[],"operators":[],"ranges":[],"related":[],"release":{"channel":"stable","label":"18.6","major":"18","manual_sha256":{"arrays.html":"0e1d5c5a7b4a949d4439995f44f74ad4264e050d16eb9ad2a850fbaee04032df","catalog-pg-type.html":"ea9c9313bab8e92a7da18ebc50c7ff1f908dd80999a7e0ca3bbc82ba626c58d4","datatype-binary.html":"0c03ec47b76e37888a3da7f5dcfd340128a5d356642a85185cc9ebe17856a904","datatype-bit.html":"c49124aad561636c18080b9e09572fde7785a6b9eae86f5a33283f0fd6774c37","datatype-boolean.html":"b633f663e6c8276e7056287640c413aa79dbc15d5353cee0d41653d58d376956","datatype-character.html":"c73eebe413ea709a7792e4cfc3cfe9fb68cb496ebe83c3ebaa9d09f8c40d0f73","datatype-datetime.html":"e366275d8b13845bfa4ef0d68faa093e5f3ecb708a1ec870cbcbf7e425b85699","datatype-enum.html":"cab38e53222e30cc94b24de4b33634fd22d7c93f58ebcf35f646c9b8da78fae8","datatype-geometric.html":"0eb3053cfe7d6b0a4b5592c5d7af8d97cae527e87f063c5793413bcba5c14cd9","datatype-json.html":"650162a05b660146b500a205d523374d00aee117a6a6240d30262be51b6e33e5","datatype-money.html":"8b29c14b90679683ac5c472b59a0a7e2853f32cc7db43fab417b2697a6879964","datatype-net-types.html":"96846e641cc37727ddcd8b8a1aa0dbe33cd9787760a2b4efb0dcb9403d5be63f","datatype-numeric.html":"b74619f7ac2f1ca9f85c48073df78c33dd30e464e61baba6008634534edcb500","datatype-oid.html":"8b3360302225f08ae72716ce1804596de95b568e5d31f788cb4597427b28cc38","datatype-pg-lsn.html":"339172f5107cc7eb7139bec911277647219e92a532cebd0f3da0a5dacf8195af","datatype-pseudo.html":"c7cf0b8214304bd8702c95f2142671ae5f0d631f83717f9c07d9e1f2579961b4","datatype-textsearch.html":"4b15e05be8b49a71b28aea22f2c46d0dbf639a91e7b4fe129a4c737c6daad47c","datatype-uuid.html":"292b307b3223c6182bdba687e7f69e6dca9038430143ad79d56327f6ed4c3246","datatype-xml.html":"061fc2295b7b69be704f0a741b8decd10ad721059f871fb0243c669bb54d7822","datatype.html":"e581f67c74e42006289638c8659e9b338adbfd7f068bd8bf2b3be3880fcd2254","domains.html":"82d486973ccc35d14276627a67ac76b434452f23f9ed4d8aa3fbb01fdf285fc2","functions-info.html":"78ac80bf81da2e4f4a33f3b850faa86f0f58ec54a083d6c31da557907de0df87","rangetypes.html":"e4960abc7ce8e51d794f94f1e57b099d30b7f75dd7f2b23a960eef91f6291c33","rowtypes.html":"74f99e029c3a9edfd3e18810ba3c8cb66d3aada91eb67b86476560871e6617e9","sql-createdomain.html":"e94f927196de1b0ea1ce35cd4603c1f6f544439adcc0edf1c4222358e832245b"},"ref":"https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2","revision":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","source_files":{"doc/src/sgml/datatype.sgml":"86328daa77e20d81d222376ec0306d9841a17104e436c08d138eb763aefb3700","src/include/catalog/pg_am.dat":"b3cb86b102a42fb0024cbd99779c9897afb40d12a71f5cc5a0e5aee7ff2f9969","src/include/catalog/pg_am.h":"3426799df799f32163fcc0765f43dbdb5e88e626acf31284fef9fc6014b69d8e","src/include/catalog/pg_cast.dat":"97911281ca2c81917394ccb2ec13367e37c6801fd461d5e1a3d2f28b911b46b2","src/include/catalog/pg_cast.h":"de585c7df687d698e7d1d100dce8a794ac9da52414ea48169191019178c8f053","src/include/catalog/pg_opclass.dat":"4ee7d3619a6aa106e1c0db55de903931c7d11b1c60ba053a022519c55f8ee668","src/include/catalog/pg_opclass.h":"9c218537806c8ef0398302faaca7f91315dfc1198c48706cebffb479b68defaf","src/include/catalog/pg_operator.dat":"5d35b9b2ef5f9797cc815263927ef8a12367fedace53e828b4e8b320e2a7f703","src/include/catalog/pg_operator.h":"621b18cfffe102413d79b746ce7a10f77fc60fdd097bac34c22b31070d3ee3c4","src/include/catalog/pg_opfamily.dat":"3b694879027b858f2ecf1fa1152b7e30c6e15a216ba99c2384291c30e9d52311","src/include/catalog/pg_opfamily.h":"e1f5fc8aebd3042df847455bf66bf755f768c2aa8a9b03d22f7ec8563eba59c0","src/include/catalog/pg_proc.dat":"1f934ce80d460159dda137714374a35f9896315ea2476709cbb4da13b38ef95a","src/include/catalog/pg_proc.h":"f7d59f07c5b95e2f3c7141762a0f576e8a3af7581ba54c27537f4d500472bea5","src/include/catalog/pg_range.dat":"5c2271f8e89e9378d1887204e5785060b77405b5f0ff3e6c719250c9c1dcfcc4","src/include/catalog/pg_range.h":"45546d952b5181f989bd9234721897fc2b37005c8e49ce2ddc156f26974c3ca4","src/include/catalog/pg_type.dat":"5f5887b75677cba2d4a1a0cfeb355df5ed91f85d385fac88bd8d7c605b3578f9","src/include/catalog/pg_type.h":"8fb198749fd82b6c1818a3c18455f226f66116fcfeb3d4959c65ce3fda22802e"},"source_sha256":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f"},"sections":[],"signature":"Arrays","sources":[{"label":"PostgreSQL 18 English manual","path":"arrays.html","sha256":"0e1d5c5a7b4a949d4439995f44f74ad4264e050d16eb9ad2a850fbaee04032df","url":"/docs/18/arrays.html"}]},"ManualEvidence":{"manual_path":"arrays.html","release":{"channel":"stable","label":"18.6","major":"18","manual_sha256":{"arrays.html":"0e1d5c5a7b4a949d4439995f44f74ad4264e050d16eb9ad2a850fbaee04032df","catalog-pg-type.html":"ea9c9313bab8e92a7da18ebc50c7ff1f908dd80999a7e0ca3bbc82ba626c58d4","datatype-binary.html":"0c03ec47b76e37888a3da7f5dcfd340128a5d356642a85185cc9ebe17856a904","datatype-bit.html":"c49124aad561636c18080b9e09572fde7785a6b9eae86f5a33283f0fd6774c37","datatype-boolean.html":"b633f663e6c8276e7056287640c413aa79dbc15d5353cee0d41653d58d376956","datatype-character.html":"c73eebe413ea709a7792e4cfc3cfe9fb68cb496ebe83c3ebaa9d09f8c40d0f73","datatype-datetime.html":"e366275d8b13845bfa4ef0d68faa093e5f3ecb708a1ec870cbcbf7e425b85699","datatype-enum.html":"cab38e53222e30cc94b24de4b33634fd22d7c93f58ebcf35f646c9b8da78fae8","datatype-geometric.html":"0eb3053cfe7d6b0a4b5592c5d7af8d97cae527e87f063c5793413bcba5c14cd9","datatype-json.html":"650162a05b660146b500a205d523374d00aee117a6a6240d30262be51b6e33e5","datatype-money.html":"8b29c14b90679683ac5c472b59a0a7e2853f32cc7db43fab417b2697a6879964","datatype-net-types.html":"96846e641cc37727ddcd8b8a1aa0dbe33cd9787760a2b4efb0dcb9403d5be63f","datatype-numeric.html":"b74619f7ac2f1ca9f85c48073df78c33dd30e464e61baba6008634534edcb500","datatype-oid.html":"8b3360302225f08ae72716ce1804596de95b568e5d31f788cb4597427b28cc38","datatype-pg-lsn.html":"339172f5107cc7eb7139bec911277647219e92a532cebd0f3da0a5dacf8195af","datatype-pseudo.html":"c7cf0b8214304bd8702c95f2142671ae5f0d631f83717f9c07d9e1f2579961b4","datatype-textsearch.html":"4b15e05be8b49a71b28aea22f2c46d0dbf639a91e7b4fe129a4c737c6daad47c","datatype-uuid.html":"292b307b3223c6182bdba687e7f69e6dca9038430143ad79d56327f6ed4c3246","datatype-xml.html":"061fc2295b7b69be704f0a741b8decd10ad721059f871fb0243c669bb54d7822","datatype.html":"e581f67c74e42006289638c8659e9b338adbfd7f068bd8bf2b3be3880fcd2254","domains.html":"82d486973ccc35d14276627a67ac76b434452f23f9ed4d8aa3fbb01fdf285fc2","functions-info.html":"78ac80bf81da2e4f4a33f3b850faa86f0f58ec54a083d6c31da557907de0df87","rangetypes.html":"e4960abc7ce8e51d794f94f1e57b099d30b7f75dd7f2b23a960eef91f6291c33","rowtypes.html":"74f99e029c3a9edfd3e18810ba3c8cb66d3aada91eb67b86476560871e6617e9","sql-createdomain.html":"e94f927196de1b0ea1ce35cd4603c1f6f544439adcc0edf1c4222358e832245b"},"ref":"https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2","revision":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","source_files":{"doc/src/sgml/datatype.sgml":"86328daa77e20d81d222376ec0306d9841a17104e436c08d138eb763aefb3700","src/include/catalog/pg_am.dat":"b3cb86b102a42fb0024cbd99779c9897afb40d12a71f5cc5a0e5aee7ff2f9969","src/include/catalog/pg_am.h":"3426799df799f32163fcc0765f43dbdb5e88e626acf31284fef9fc6014b69d8e","src/include/catalog/pg_cast.dat":"97911281ca2c81917394ccb2ec13367e37c6801fd461d5e1a3d2f28b911b46b2","src/include/catalog/pg_cast.h":"de585c7df687d698e7d1d100dce8a794ac9da52414ea48169191019178c8f053","src/include/catalog/pg_opclass.dat":"4ee7d3619a6aa106e1c0db55de903931c7d11b1c60ba053a022519c55f8ee668","src/include/catalog/pg_opclass.h":"9c218537806c8ef0398302faaca7f91315dfc1198c48706cebffb479b68defaf","src/include/catalog/pg_operator.dat":"5d35b9b2ef5f9797cc815263927ef8a12367fedace53e828b4e8b320e2a7f703","src/include/catalog/pg_operator.h":"621b18cfffe102413d79b746ce7a10f77fc60fdd097bac34c22b31070d3ee3c4","src/include/catalog/pg_opfamily.dat":"3b694879027b858f2ecf1fa1152b7e30c6e15a216ba99c2384291c30e9d52311","src/include/catalog/pg_opfamily.h":"e1f5fc8aebd3042df847455bf66bf755f768c2aa8a9b03d22f7ec8563eba59c0","src/include/catalog/pg_proc.dat":"1f934ce80d460159dda137714374a35f9896315ea2476709cbb4da13b38ef95a","src/include/catalog/pg_proc.h":"f7d59f07c5b95e2f3c7141762a0f576e8a3af7581ba54c27537f4d500472bea5","src/include/catalog/pg_range.dat":"5c2271f8e89e9378d1887204e5785060b77405b5f0ff3e6c719250c9c1dcfcc4","src/include/catalog/pg_range.h":"45546d952b5181f989bd9234721897fc2b37005c8e49ce2ddc156f26974c3ca4","src/include/catalog/pg_type.dat":"5f5887b75677cba2d4a1a0cfeb355df5ed91f85d385fac88bd8d7c605b3578f9","src/include/catalog/pg_type.h":"8fb198749fd82b6c1818a3c18455f226f66116fcfeb3d4959c65ce3fda22802e"},"source_sha256":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f"},"sources":[{"label":"PostgreSQL 18 English manual","path":"arrays.html","sha256":"0e1d5c5a7b4a949d4439995f44f74ad4264e050d16eb9ad2a850fbaee04032df","url":"/docs/18/arrays.html"}]},"MeasuredEvidence":{}},"Text":{"Collection":"type","Key":"arrays","SourceDatabase":"center","Version":"18","Locale":"en","Title":"Arrays","Summary":"PostgreSQL allows columns of a table to be defined as variable-length multidimensional arrays. Arrays of any built-in or user-defined base type, enum type, composite type, range type, or domain can be created.","BodyHTML":"\u003cdiv id=\"ARRAYS\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch2\u003e8.15. Arrays \u003c/h2\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\n\u003cp\u003e\u003cspan\u003ePostgreSQL\u003c/span\u003e allows columns of a table to be defined as variable-length multidimensional arrays. Arrays of any built-in or user-defined base type, enum type, composite type, range type, or domain can be created.\u003c/p\u003e\n\u003cdiv id=\"ARRAYS-DECLARATION\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3\u003e8.15.1. Declaration of Array Types \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eTo illustrate the use of array types, we create this table:\u003c/p\u003e\n\u003cpre\u003eCREATE TABLE sal_emp (\n    name            text,\n    pay_by_quarter  integer[],\n    schedule        text[][]\n);\n\u003c/pre\u003e\n\u003cp\u003eAs shown, an array data type is named by appending square brackets (\u003ccode\u003e[]\u003c/code\u003e) to the data type name of the array elements. The above command will create a table named \u003ccode\u003esal_emp\u003c/code\u003e with a column of type \u003ccode\u003etext\u003c/code\u003e (\u003ccode\u003ename\u003c/code\u003e), a one-dimensional array of type \u003ccode\u003einteger\u003c/code\u003e (\u003ccode\u003epay_by_quarter\u003c/code\u003e), which represents the employee\u0026#39;s salary by quarter, and a two-dimensional array of \u003ccode\u003etext\u003c/code\u003e (\u003ccode\u003eschedule\u003c/code\u003e), which represents the employee\u0026#39;s weekly schedule.\u003c/p\u003e\n\u003cp\u003eThe syntax for \u003ccode\u003eCREATE TABLE\u003c/code\u003e allows the exact size of arrays to be specified, for example:\u003c/p\u003e\n\u003cpre\u003eCREATE TABLE tictactoe (\n    squares   integer[3][3]\n);\n\u003c/pre\u003e\n\u003cp\u003eHowever, the current implementation ignores any supplied array size limits, i.e., the behavior is the same as for arrays of unspecified length.\u003c/p\u003e\n\u003cp\u003eThe current implementation does not enforce the declared number of dimensions either. Arrays of a particular element type are all considered to be of the same type, regardless of size or number of dimensions. So, declaring the array size or number of dimensions in \u003ccode\u003eCREATE TABLE\u003c/code\u003e is simply documentation; it does not affect run-time behavior.\u003c/p\u003e\n\u003cp\u003eAn alternative syntax, which conforms to the SQL standard by using the keyword \u003ccode\u003eARRAY\u003c/code\u003e, can be used for one-dimensional arrays. \u003ccode\u003epay_by_quarter\u003c/code\u003e could have been defined as:\u003c/p\u003e\n\u003cpre\u003e    pay_by_quarter  integer ARRAY[4],\n\u003c/pre\u003e\n\u003cp\u003eOr, if no array size is to be specified:\u003c/p\u003e\n\u003cpre\u003e    pay_by_quarter  integer ARRAY,\n\u003c/pre\u003e\n\u003cp\u003eAs before, however, \u003cspan\u003ePostgreSQL\u003c/span\u003e does not enforce the size restriction in any case.\u003c/p\u003e\n\u003c/div\u003e\n\u003cdiv id=\"ARRAYS-INPUT\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3\u003e8.15.2. Array Value Input \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eTo write an array value as a literal constant, enclose the element values within curly braces and separate them by commas. (If you know C, this is not unlike the C syntax for initializing structures.) You can put double quotes around any element value, and must do so if it contains commas or curly braces. (More details appear below.) Thus, the general format of an array constant is the following:\u003c/p\u003e\n\u003cpre\u003e\u0026#39;{ \u003cem\u003e\u003ccode\u003eval1\u003c/code\u003e\u003c/em\u003e \u003cem\u003e\u003ccode\u003edelim\u003c/code\u003e\u003c/em\u003e \u003cem\u003e\u003ccode\u003eval2\u003c/code\u003e\u003c/em\u003e \u003cem\u003e\u003ccode\u003edelim\u003c/code\u003e\u003c/em\u003e ... }\u0026#39;\n\u003c/pre\u003e\n\u003cp\u003ewhere \u003cem\u003e\u003ccode\u003edelim\u003c/code\u003e\u003c/em\u003e is the delimiter character for the type, as recorded in its \u003ccode\u003epg_type\u003c/code\u003e entry. Among the standard data types provided in the \u003cspan\u003ePostgreSQL\u003c/span\u003e distribution, all use a comma (\u003ccode\u003e,\u003c/code\u003e), except for type \u003ccode\u003ebox\u003c/code\u003e which uses a semicolon (\u003ccode\u003e;\u003c/code\u003e). Each \u003cem\u003e\u003ccode\u003eval\u003c/code\u003e\u003c/em\u003e is either a constant of the array element type, or a subarray. An example of an array constant is:\u003c/p\u003e\n\u003cpre\u003e\u0026#39;{{1,2,3},{4,5,6},{7,8,9}}\u0026#39;\n\u003c/pre\u003e\n\u003cp\u003eThis constant is a two-dimensional, 3-by-3 array consisting of three subarrays of integers.\u003c/p\u003e\n\u003cp\u003eTo set an element of an array constant to NULL, write \u003ccode\u003eNULL\u003c/code\u003e for the element value. (Any upper- or lower-case variant of \u003ccode\u003eNULL\u003c/code\u003e will do.) If you want an actual string value \u003cspan\u003e“\u003cspan\u003eNULL\u003c/span\u003e”\u003c/span\u003e, you must put double quotes around it.\u003c/p\u003e\n\u003cp\u003e(These kinds of array constants are actually only a special case of the generic type constants discussed in \u003ca href=\"/docs/18/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS-GENERIC\" rel=\"nofollow\"\u003eSection 4.1.2.7\u003c/a\u003e. The constant is initially treated as a string and passed to the array input conversion routine. An explicit type specification might be necessary.)\u003c/p\u003e\n\u003cp\u003eNow we can show some \u003ccode\u003eINSERT\u003c/code\u003e statements:\u003c/p\u003e\n\u003cpre\u003eINSERT INTO sal_emp\n    VALUES (\u0026#39;Bill\u0026#39;,\n    \u0026#39;{10000, 10000, 10000, 10000}\u0026#39;,\n    \u0026#39;{{\u0026#34;meeting\u0026#34;, \u0026#34;lunch\u0026#34;}, {\u0026#34;training\u0026#34;, \u0026#34;presentation\u0026#34;}}\u0026#39;);\n\nINSERT INTO sal_emp\n    VALUES (\u0026#39;Carol\u0026#39;,\n    \u0026#39;{20000, 25000, 25000, 25000}\u0026#39;,\n    \u0026#39;{{\u0026#34;breakfast\u0026#34;, \u0026#34;consulting\u0026#34;}, {\u0026#34;meeting\u0026#34;, \u0026#34;lunch\u0026#34;}}\u0026#39;);\n\u003c/pre\u003e\n\u003cp\u003eThe result of the previous two inserts looks like this:\u003c/p\u003e\n\u003cpre\u003eSELECT * FROM sal_emp;\n name  |      pay_by_quarter       |                 schedule\n-------+---------------------------+-------------------------------------------\n Bill  | {10000,10000,10000,10000} | {{meeting,lunch},{training,presentation}}\n Carol | {20000,25000,25000,25000} | {{breakfast,consulting},{meeting,lunch}}\n(2 rows)\n\u003c/pre\u003e\n\u003cp\u003eMultidimensional arrays must have matching extents for each dimension. A mismatch causes an error, for example:\u003c/p\u003e\n\u003cpre\u003eINSERT INTO sal_emp\n    VALUES (\u0026#39;Bill\u0026#39;,\n    \u0026#39;{10000, 10000, 10000, 10000}\u0026#39;,\n    \u0026#39;{{\u0026#34;meeting\u0026#34;, \u0026#34;lunch\u0026#34;}, {\u0026#34;meeting\u0026#34;}}\u0026#39;);\nERROR:  malformed array literal: \u0026#34;{{\u0026#34;meeting\u0026#34;, \u0026#34;lunch\u0026#34;}, {\u0026#34;meeting\u0026#34;}}\u0026#34;\nDETAIL:  Multidimensional arrays must have sub-arrays with matching dimensions.\n\u003c/pre\u003e\n\u003cp\u003eThe \u003ccode\u003eARRAY\u003c/code\u003e constructor syntax can also be used:\u003c/p\u003e\n\u003cpre\u003eINSERT INTO sal_emp\n    VALUES (\u0026#39;Bill\u0026#39;,\n    ARRAY[10000, 10000, 10000, 10000],\n    ARRAY[[\u0026#39;meeting\u0026#39;, \u0026#39;lunch\u0026#39;], [\u0026#39;training\u0026#39;, \u0026#39;presentation\u0026#39;]]);\n\nINSERT INTO sal_emp\n    VALUES (\u0026#39;Carol\u0026#39;,\n    ARRAY[20000, 25000, 25000, 25000],\n    ARRAY[[\u0026#39;breakfast\u0026#39;, \u0026#39;consulting\u0026#39;], [\u0026#39;meeting\u0026#39;, \u0026#39;lunch\u0026#39;]]);\n\u003c/pre\u003e\n\u003cp\u003eNotice that the array elements are ordinary SQL constants or expressions; for instance, string literals are single quoted, instead of double quoted as they would be in an array literal. The \u003ccode\u003eARRAY\u003c/code\u003e constructor syntax is discussed in more detail in \u003ca href=\"/docs/18/sql-expressions.html#SQL-SYNTAX-ARRAY-CONSTRUCTORS\" rel=\"nofollow\"\u003eSection 4.2.12\u003c/a\u003e.\u003c/p\u003e\n\u003c/div\u003e\n\u003cdiv id=\"ARRAYS-ACCESSING\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3\u003e8.15.3. Accessing Arrays \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eNow, we can run some queries on the table. First, we show how to access a single element of an array. This query retrieves the names of the employees whose pay changed in the second quarter:\u003c/p\u003e\n\u003cpre\u003eSELECT name FROM sal_emp WHERE pay_by_quarter[1] \u0026lt;\u0026gt; pay_by_quarter[2];\n\n name\n-------\n Carol\n(1 row)\n\u003c/pre\u003e\n\u003cp\u003eThe array subscript numbers are written within square brackets. By default \u003cspan\u003ePostgreSQL\u003c/span\u003e uses a one-based numbering convention for arrays, that is, an array of \u003cem\u003e\u003ccode\u003en\u003c/code\u003e\u003c/em\u003e elements starts with \u003ccode\u003earray[1]\u003c/code\u003e and ends with \u003ccode\u003earray[\u003cem\u003e\u003ccode\u003en\u003c/code\u003e\u003c/em\u003e]\u003c/code\u003e.\u003c/p\u003e\n\u003cp\u003eThis query retrieves the third quarter pay of all employees:\u003c/p\u003e\n\u003cpre\u003eSELECT pay_by_quarter[3] FROM sal_emp;\n\n pay_by_quarter\n----------------\n          10000\n          25000\n(2 rows)\n\u003c/pre\u003e\n\u003cp\u003eWe can also access arbitrary rectangular slices of an array, or subarrays. An array slice is denoted by writing \u003ccode\u003e\u003cem\u003e\u003ccode\u003elower-bound\u003c/code\u003e\u003c/em\u003e:\u003cem\u003e\u003ccode\u003eupper-bound\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e for one or more array dimensions. For example, this query retrieves the first item on Bill\u0026#39;s schedule for the first two days of the week:\u003c/p\u003e\n\u003cpre\u003eSELECT schedule[1:2][1:1] FROM sal_emp WHERE name = \u0026#39;Bill\u0026#39;;\n\n        schedule\n------------------------\n {{meeting},{training}}\n(1 row)\n\u003c/pre\u003e\n\u003cp\u003eIf any dimension is written as a slice, i.e., contains a colon, then all dimensions are treated as slices. Any dimension that has only a single number (no colon) is treated as being from 1 to the number specified. For example, \u003ccode\u003e[2]\u003c/code\u003e is treated as \u003ccode\u003e[1:2]\u003c/code\u003e, as in this example:\u003c/p\u003e\n\u003cpre\u003eSELECT schedule[1:2][2] FROM sal_emp WHERE name = \u0026#39;Bill\u0026#39;;\n\n                 schedule\n-------------------------------------------\n {{meeting,lunch},{training,presentation}}\n(1 row)\n\u003c/pre\u003e\n\u003cp\u003eTo avoid confusion with the non-slice case, it\u0026#39;s best to use slice syntax for all dimensions, e.g., \u003ccode\u003e[1:2][1:1]\u003c/code\u003e, not \u003ccode\u003e[2][1:1]\u003c/code\u003e.\u003c/p\u003e\n\u003cp\u003eIt is possible to omit the \u003cem\u003e\u003ccode\u003elower-bound\u003c/code\u003e\u003c/em\u003e and/or \u003cem\u003e\u003ccode\u003eupper-bound\u003c/code\u003e\u003c/em\u003e of a slice specifier; the missing bound is replaced by the lower or upper limit of the array\u0026#39;s subscripts. For example:\u003c/p\u003e\n\u003cpre\u003eSELECT schedule[:2][2:] FROM sal_emp WHERE name = \u0026#39;Bill\u0026#39;;\n\n        schedule\n------------------------\n {{lunch},{presentation}}\n(1 row)\n\nSELECT schedule[:][1:1] FROM sal_emp WHERE name = \u0026#39;Bill\u0026#39;;\n\n        schedule\n------------------------\n {{meeting},{training}}\n(1 row)\n\u003c/pre\u003e\n\u003cp\u003eAn 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 \u003ccode\u003eschedule\u003c/code\u003e currently has the dimensions \u003ccode\u003e[1:3][1:2]\u003c/code\u003e then referencing \u003ccode\u003eschedule[3][3]\u003c/code\u003e yields NULL. Similarly, an array reference with the wrong number of subscripts yields a null rather than an error.\u003c/p\u003e\n\u003cp\u003eAn array slice expression likewise yields null if the array itself or any of the subscript expressions are null. However, in other 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. (This does not match non-slice behavior and is done for historical reasons.) If the requested slice partially overlaps the array bounds, then it is silently reduced to just the overlapping region instead of returning null.\u003c/p\u003e\n\u003cp\u003eThe current dimensions of any array value can be retrieved with the \u003ccode\u003earray_dims\u003c/code\u003e function:\u003c/p\u003e\n\u003cpre\u003eSELECT array_dims(schedule) FROM sal_emp WHERE name = \u0026#39;Carol\u0026#39;;\n\n array_dims\n------------\n [1:2][1:2]\n(1 row)\n\u003c/pre\u003e\n\u003cp\u003e\u003ccode\u003earray_dims\u003c/code\u003e produces a \u003ccode\u003etext\u003c/code\u003e result, which is convenient for people to read but perhaps inconvenient for programs. Dimensions can also be retrieved with \u003ccode\u003earray_upper\u003c/code\u003e and \u003ccode\u003earray_lower\u003c/code\u003e, which return the upper and lower bound of a specified array dimension, respectively:\u003c/p\u003e\n\u003cpre\u003eSELECT array_upper(schedule, 1) FROM sal_emp WHERE name = \u0026#39;Carol\u0026#39;;\n\n array_upper\n-------------\n           2\n(1 row)\n\u003c/pre\u003e\n\u003cp\u003e\u003ccode\u003earray_length\u003c/code\u003e will return the length of a specified array dimension:\u003c/p\u003e\n\u003cpre\u003eSELECT array_length(schedule, 1) FROM sal_emp WHERE name = \u0026#39;Carol\u0026#39;;\n\n array_length\n--------------\n            2\n(1 row)\n\u003c/pre\u003e\n\u003cp\u003e\u003ccode\u003ecardinality\u003c/code\u003e returns the total number of elements in an array across all dimensions. It is effectively the number of rows a call to \u003ccode\u003eunnest\u003c/code\u003e would yield:\u003c/p\u003e\n\u003cpre\u003eSELECT cardinality(schedule) FROM sal_emp WHERE name = \u0026#39;Carol\u0026#39;;\n\n cardinality\n-------------\n           4\n(1 row)\n\u003c/pre\u003e\n\u003c/div\u003e\n\u003cdiv id=\"ARRAYS-MODIFYING\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3\u003e8.15.4. Modifying Arrays \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eAn array value can be replaced completely:\u003c/p\u003e\n\u003cpre\u003eUPDATE sal_emp SET pay_by_quarter = \u0026#39;{25000,25000,27000,27000}\u0026#39;\n    WHERE name = \u0026#39;Carol\u0026#39;;\n\u003c/pre\u003e\n\u003cp\u003eor using the \u003ccode\u003eARRAY\u003c/code\u003e expression syntax:\u003c/p\u003e\n\u003cpre\u003eUPDATE sal_emp SET pay_by_quarter = ARRAY[25000,25000,27000,27000]\n    WHERE name = \u0026#39;Carol\u0026#39;;\n\u003c/pre\u003e\n\u003cp\u003eAn array can also be updated at a single element:\u003c/p\u003e\n\u003cpre\u003eUPDATE sal_emp SET pay_by_quarter[4] = 15000\n    WHERE name = \u0026#39;Bill\u0026#39;;\n\u003c/pre\u003e\n\u003cp\u003eor updated in a slice:\u003c/p\u003e\n\u003cpre\u003eUPDATE sal_emp SET pay_by_quarter[1:2] = \u0026#39;{27000,27000}\u0026#39;\n    WHERE name = \u0026#39;Carol\u0026#39;;\n\u003c/pre\u003e\n\u003cp\u003eThe slice syntaxes with omitted \u003cem\u003e\u003ccode\u003elower-bound\u003c/code\u003e\u003c/em\u003e and/or \u003cem\u003e\u003ccode\u003eupper-bound\u003c/code\u003e\u003c/em\u003e can be used too, but only when updating an array value that is not NULL or zero-dimensional (otherwise, there is no existing subscript limit to substitute).\u003c/p\u003e\n\u003cp\u003eA stored array value can be enlarged by assigning to elements not already present. Any positions between those previously present and the newly assigned elements will be filled with nulls. For example, if array \u003ccode\u003emyarray\u003c/code\u003e currently has 4 elements, it will have six elements after an update that assigns to \u003ccode\u003emyarray[6]\u003c/code\u003e; \u003ccode\u003emyarray[5]\u003c/code\u003e will contain null. Currently, enlargement in this fashion is only allowed for one-dimensional arrays, not multidimensional arrays.\u003c/p\u003e\n\u003cp\u003eSubscripted assignment allows creation of arrays that do not use one-based subscripts. For example one might assign to \u003ccode\u003emyarray[-2:7]\u003c/code\u003e to create an array with subscript values from -2 to 7.\u003c/p\u003e\n\u003cp\u003eNew array values can also be constructed using the concatenation operator, \u003ccode\u003e||\u003c/code\u003e:\u003c/p\u003e\n\u003cpre\u003eSELECT ARRAY[1,2] || ARRAY[3,4];\n ?column?\n-----------\n {1,2,3,4}\n(1 row)\n\nSELECT ARRAY[5,6] || ARRAY[[1,2],[3,4]];\n      ?column?\n---------------------\n {{5,6},{1,2},{3,4}}\n(1 row)\n\u003c/pre\u003e\n\u003cp\u003eThe concatenation operator allows a single element to be pushed onto the beginning or end of a one-dimensional array. It also accepts two \u003cem\u003e\u003ccode\u003eN\u003c/code\u003e\u003c/em\u003e-dimensional arrays, or an \u003cem\u003e\u003ccode\u003eN\u003c/code\u003e\u003c/em\u003e-dimensional and an \u003cem\u003e\u003ccode\u003eN+1\u003c/code\u003e\u003c/em\u003e-dimensional array.\u003c/p\u003e\n\u003cp\u003eWhen a single element is pushed onto either the beginning or end of a one-dimensional array, the result is an array with the same lower bound subscript as the array operand. For example:\u003c/p\u003e\n\u003cpre\u003eSELECT array_dims(1 || \u0026#39;[0:1]={2,3}\u0026#39;::int[]);\n array_dims\n------------\n [0:2]\n(1 row)\n\nSELECT array_dims(ARRAY[1,2] || 3);\n array_dims\n------------\n [1:3]\n(1 row)\n\u003c/pre\u003e\n\u003cp\u003eWhen two arrays with an equal number of dimensions are concatenated, the result retains the lower bound subscript of the left-hand operand\u0026#39;s outer dimension. The result is an array comprising every element of the left-hand operand followed by every element of the right-hand operand. For example:\u003c/p\u003e\n\u003cpre\u003eSELECT array_dims(ARRAY[1,2] || ARRAY[3,4,5]);\n array_dims\n------------\n [1:5]\n(1 row)\n\nSELECT array_dims(ARRAY[[1,2],[3,4]] || ARRAY[[5,6],[7,8],[9,0]]);\n array_dims\n------------\n [1:5][1:2]\n(1 row)\n\u003c/pre\u003e\n\u003cp\u003eWhen an \u003cem\u003e\u003ccode\u003eN\u003c/code\u003e\u003c/em\u003e-dimensional array is pushed onto the beginning or end of an \u003cem\u003e\u003ccode\u003eN+1\u003c/code\u003e\u003c/em\u003e-dimensional array, the result is analogous to the element-array case above. Each \u003cem\u003e\u003ccode\u003eN\u003c/code\u003e\u003c/em\u003e-dimensional sub-array is essentially an element of the \u003cem\u003e\u003ccode\u003eN+1\u003c/code\u003e\u003c/em\u003e-dimensional array\u0026#39;s outer dimension. For example:\u003c/p\u003e\n\u003cpre\u003eSELECT array_dims(ARRAY[1,2] || ARRAY[[3,4],[5,6]]);\n array_dims\n------------\n [1:3][1:2]\n(1 row)\n\u003c/pre\u003e\n\u003cp\u003eAn array can also be constructed by using the functions \u003ccode\u003earray_prepend\u003c/code\u003e, \u003ccode\u003earray_append\u003c/code\u003e, or \u003ccode\u003earray_cat\u003c/code\u003e. The first two only support one-dimensional arrays, but \u003ccode\u003earray_cat\u003c/code\u003e supports multidimensional arrays. Some examples:\u003c/p\u003e\n\u003cpre\u003eSELECT array_prepend(1, ARRAY[2,3]);\n array_prepend\n---------------\n {1,2,3}\n(1 row)\n\nSELECT array_append(ARRAY[1,2], 3);\n array_append\n--------------\n {1,2,3}\n(1 row)\n\nSELECT array_cat(ARRAY[1,2], ARRAY[3,4]);\n array_cat\n-----------\n {1,2,3,4}\n(1 row)\n\nSELECT array_cat(ARRAY[[1,2],[3,4]], ARRAY[5,6]);\n      array_cat\n---------------------\n {{1,2},{3,4},{5,6}}\n(1 row)\n\nSELECT array_cat(ARRAY[5,6], ARRAY[[1,2],[3,4]]);\n      array_cat\n---------------------\n {{5,6},{1,2},{3,4}}\n\u003c/pre\u003e\n\u003cp\u003eIn simple cases, the concatenation operator discussed above is preferred over direct use of these functions. However, because the concatenation operator is overloaded to serve all three cases, there are situations where use of one of the functions is helpful to avoid ambiguity. For example consider:\u003c/p\u003e\n\u003cpre\u003eSELECT ARRAY[1, 2] || \u0026#39;{3, 4}\u0026#39;;  -- the untyped literal is taken as an array\n ?column?\n-----------\n {1,2,3,4}\n\nSELECT ARRAY[1, 2] || \u0026#39;7\u0026#39;;                 -- so is this one\nERROR:  malformed array literal: \u0026#34;7\u0026#34;\n\nSELECT ARRAY[1, 2] || NULL;                -- so is an undecorated NULL\n ?column?\n----------\n {1,2}\n(1 row)\n\nSELECT array_append(ARRAY[1, 2], NULL);    -- this might have been meant\n array_append\n--------------\n {1,2,NULL}\n\u003c/pre\u003e\n\u003cp\u003eIn the examples above, the parser sees an integer array on one side of the concatenation operator, and a constant of undetermined type on the other. The heuristic it uses to resolve the constant\u0026#39;s type is to assume it\u0026#39;s of the same type as the operator\u0026#39;s other input — in this case, integer array. So the concatenation operator is presumed to represent \u003ccode\u003earray_cat\u003c/code\u003e, not \u003ccode\u003earray_append\u003c/code\u003e. When that\u0026#39;s the wrong choice, it could be fixed by casting the constant to the array\u0026#39;s element type; but explicit use of \u003ccode\u003earray_append\u003c/code\u003e might be a preferable solution.\u003c/p\u003e\n\u003c/div\u003e\n\u003cdiv id=\"ARRAYS-SEARCHING\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3\u003e8.15.5. Searching in Arrays \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eTo search for a value in an array, each value must be checked. This can be done manually, if you know the size of the array. For example:\u003c/p\u003e\n\u003cpre\u003eSELECT * FROM sal_emp WHERE pay_by_quarter[1] = 10000 OR\n                            pay_by_quarter[2] = 10000 OR\n                            pay_by_quarter[3] = 10000 OR\n                            pay_by_quarter[4] = 10000;\n\u003c/pre\u003e\n\u003cp\u003eHowever, this quickly becomes tedious for large arrays, and is not helpful if the size of the array is unknown. An alternative method is described in \u003ca href=\"/docs/18/functions-comparisons.html\" rel=\"nofollow\"\u003eSection 9.25\u003c/a\u003e. The above query could be replaced by:\u003c/p\u003e\n\u003cpre\u003eSELECT * FROM sal_emp WHERE 10000 = ANY (pay_by_quarter);\n\u003c/pre\u003e\n\u003cp\u003eIn addition, you can find rows where the array has all values equal to 10000 with:\u003c/p\u003e\n\u003cpre\u003eSELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter);\n\u003c/pre\u003e\n\u003cp\u003eAlternatively, the \u003ccode\u003egenerate_subscripts\u003c/code\u003e function can be used. For example:\u003c/p\u003e\n\u003cpre\u003eSELECT * FROM\n   (SELECT pay_by_quarter,\n           generate_subscripts(pay_by_quarter, 1) AS s\n      FROM sal_emp) AS foo\n WHERE pay_by_quarter[s] = 10000;\n\u003c/pre\u003e\n\u003cp\u003eThis function is described in \u003ca href=\"/docs/18/functions-srf.html#FUNCTIONS-SRF-SUBSCRIPTS\" rel=\"nofollow\"\u003eTable 9.70\u003c/a\u003e.\u003c/p\u003e\n\u003cp\u003eYou can also search an array using the \u003ccode\u003e\u0026amp;\u0026amp;\u003c/code\u003e operator, which checks whether the left operand overlaps with the right operand. For instance:\u003c/p\u003e\n\u003cpre\u003eSELECT * FROM sal_emp WHERE pay_by_quarter \u0026amp;\u0026amp; ARRAY[10000];\n\u003c/pre\u003e\n\u003cp\u003eThis and other array operators are further described in \u003ca href=\"/docs/18/functions-array.html\" rel=\"nofollow\"\u003eSection 9.19\u003c/a\u003e. It can be accelerated by an appropriate index, as described in \u003ca href=\"/docs/18/indexes-types.html\" rel=\"nofollow\"\u003eSection 11.2\u003c/a\u003e.\u003c/p\u003e\n\u003cp\u003eYou can also search for specific values in an array using the \u003ccode\u003earray_position\u003c/code\u003e and \u003ccode\u003earray_positions\u003c/code\u003e functions. The former returns the subscript of the first occurrence of a value in an array; the latter returns an array with the subscripts of all occurrences of the value in the array. For example:\u003c/p\u003e\n\u003cpre\u003eSELECT array_position(ARRAY[\u0026#39;sun\u0026#39;,\u0026#39;mon\u0026#39;,\u0026#39;tue\u0026#39;,\u0026#39;wed\u0026#39;,\u0026#39;thu\u0026#39;,\u0026#39;fri\u0026#39;,\u0026#39;sat\u0026#39;], \u0026#39;mon\u0026#39;);\n array_position\n----------------\n              2\n(1 row)\n\nSELECT array_positions(ARRAY[1, 4, 3, 1, 3, 4, 2, 1], 1);\n array_positions\n-----------------\n {1,4,8}\n(1 row)\n\u003c/pre\u003e\n\u003cdiv\u003e\n\u003ch3\u003eTip\u003c/h3\u003e\n\u003cp\u003eArrays are not sets; searching for specific array elements can be a sign of database misdesign. Consider using a separate table with a row for each item that would be an array element. This will be easier to search, and is likely to scale better for a large number of elements.\u003c/p\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cdiv id=\"ARRAYS-IO\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3\u003e8.15.6. Array Input and Output Syntax \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eThe external text representation of an array value consists of items that are interpreted according to the I/O conversion rules for the array\u0026#39;s element type, plus decoration that indicates the array structure. The decoration consists of curly braces (\u003ccode\u003e{\u003c/code\u003e and \u003ccode\u003e}\u003c/code\u003e) around the array value plus delimiter characters between adjacent items. The delimiter character is usually a comma (\u003ccode\u003e,\u003c/code\u003e) but can be something else: it is determined by the \u003ccode\u003etypdelim\u003c/code\u003e setting for the array\u0026#39;s element type. Among the standard data types provided in the \u003cspan\u003ePostgreSQL\u003c/span\u003e distribution, all use a comma, except for type \u003ccode\u003ebox\u003c/code\u003e, which uses a semicolon (\u003ccode\u003e;\u003c/code\u003e). In a multidimensional array, each dimension (row, plane, cube, etc.) gets its own level of curly braces, and delimiters must be written between adjacent curly-braced entities of the same level.\u003c/p\u003e\n\u003cp\u003eThe array output routine will put double quotes around element values if they are empty strings, contain curly braces, delimiter characters, double quotes, backslashes, or white space, or match the word \u003ccode\u003eNULL\u003c/code\u003e. 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 the presence or absence of quotes.\u003c/p\u003e\n\u003cp\u003eBy default, the lower bound index value of an array\u0026#39;s dimensions is 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 (\u003ccode\u003e[]\u003c/code\u003e) around each array dimension\u0026#39;s lower and upper bounds, with a colon (\u003ccode\u003e:\u003c/code\u003e) delimiter character in between. The array dimension decoration is followed by an equal sign (\u003ccode\u003e=\u003c/code\u003e). For example:\u003c/p\u003e\n\u003cpre\u003eSELECT f1[1][-2][3] AS e1, f1[1][-1][5] AS e2\n FROM (SELECT \u0026#39;[1:1][-2:-1][3:5]={{{1,2,3},{4,5,6}}}\u0026#39;::int[] AS f1) AS ss;\n\n e1 | e2\n----+----\n  1 |  6\n(1 row)\n\u003c/pre\u003e\n\u003cp\u003eThe array output routine will include explicit dimensions in its result only when there are one or more lower bounds different from one.\u003c/p\u003e\n\u003cp\u003eIf the value written for an element is \u003ccode\u003eNULL\u003c/code\u003e (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 \u003cspan\u003e“\u003cspan\u003eNULL\u003c/span\u003e”\u003c/span\u003e to be entered. Also, for backward compatibility with pre-8.2 versions of \u003cspan\u003ePostgreSQL\u003c/span\u003e, the \u003ca href=\"/docs/18/runtime-config-compatible.html#GUC-ARRAY-NULLS\" rel=\"nofollow\"\u003earray_nulls\u003c/a\u003e configuration parameter can be turned \u003ccode\u003eoff\u003c/code\u003e to suppress recognition of \u003ccode\u003eNULL\u003c/code\u003e as a NULL.\u003c/p\u003e\n\u003cp\u003eAs shown previously, when writing an array value you can use double quotes around any individual array element. You \u003cspan\u003e\u003cem\u003emust\u003c/em\u003e\u003c/span\u003e do so if the element value would otherwise confuse the array-value parser. For example, elements containing curly braces, commas (or the data type\u0026#39;s delimiter character), double quotes, backslashes, or leading or trailing whitespace must be double-quoted. Empty strings and strings matching the word \u003ccode\u003eNULL\u003c/code\u003e 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 avoid quotes and use backslash-escaping to protect all data characters that would otherwise be taken as array syntax.\u003c/p\u003e\n\u003cp\u003eYou can add whitespace before a left brace or after a right brace. You can also add whitespace before or after any individual item string. In all of these cases the whitespace will be ignored. However, whitespace within double-quoted elements, or surrounded on both sides by non-whitespace characters of an element, is not ignored.\u003c/p\u003e\n\u003cdiv\u003e\n\u003ch3\u003eTip\u003c/h3\u003e\n\u003cp\u003eThe \u003ccode\u003eARRAY\u003c/code\u003e constructor syntax (see \u003ca href=\"/docs/18/sql-expressions.html#SQL-SYNTAX-ARRAY-CONSTRUCTORS\" rel=\"nofollow\"\u003eSection 4.2.12\u003c/a\u003e) is often easier to work with than the array-literal syntax when writing array values in SQL commands. In \u003ccode\u003eARRAY\u003c/code\u003e, individual element values are written the same way they would be written when not members of an array.\u003c/p\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e","SourceRevision":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","ContentHash":"cbc5be3704037b33c4b45701e9332347d802fafe5424cc12d139be4f8697091f","Payload":{"description":["PostgreSQL allows columns of a table to be defined as variable-length multidimensional arrays. Arrays of any built-in or user-defined base type, enum type, composite type, range type, or domain can be created."],"manual_html":"\u003cdiv class=\"sect1\" id=\"ARRAYS\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch2 class=\"title\"\u003e8.15. Arrays \u003c/h2\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\n\u003cp\u003e\u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e allows columns of a table to be defined as variable-length multidimensional arrays. Arrays of any built-in or user-defined base type, enum type, composite type, range type, or domain can be created.\u003c/p\u003e\n\u003cdiv class=\"sect2\" id=\"ARRAYS-DECLARATION\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3 class=\"title\"\u003e8.15.1. Declaration of Array Types \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eTo illustrate the use of array types, we create this table:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eCREATE TABLE sal_emp (\n    name            text,\n    pay_by_quarter  integer[],\n    schedule        text[][]\n);\n\u003c/pre\u003e\n\u003cp\u003eAs shown, an array data type is named by appending square brackets (\u003ccode class=\"literal\"\u003e[]\u003c/code\u003e) to the data type name of the array elements. The above command will create a table named \u003ccode class=\"structname\"\u003esal_emp\u003c/code\u003e with a column of type \u003ccode class=\"type\"\u003etext\u003c/code\u003e (\u003ccode class=\"structfield\"\u003ename\u003c/code\u003e), a one-dimensional array of type \u003ccode class=\"type\"\u003einteger\u003c/code\u003e (\u003ccode class=\"structfield\"\u003epay_by_quarter\u003c/code\u003e), which represents the employee's salary by quarter, and a two-dimensional array of \u003ccode class=\"type\"\u003etext\u003c/code\u003e (\u003ccode class=\"structfield\"\u003eschedule\u003c/code\u003e), which represents the employee's weekly schedule.\u003c/p\u003e\n\u003cp\u003eThe syntax for \u003ccode class=\"command\"\u003eCREATE TABLE\u003c/code\u003e allows the exact size of arrays to be specified, for example:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eCREATE TABLE tictactoe (\n    squares   integer[3][3]\n);\n\u003c/pre\u003e\n\u003cp\u003eHowever, the current implementation ignores any supplied array size limits, i.e., the behavior is the same as for arrays of unspecified length.\u003c/p\u003e\n\u003cp\u003eThe current implementation does not enforce the declared number of dimensions either. Arrays of a particular element type are all considered to be of the same type, regardless of size or number of dimensions. So, declaring the array size or number of dimensions in \u003ccode class=\"command\"\u003eCREATE TABLE\u003c/code\u003e is simply documentation; it does not affect run-time behavior.\u003c/p\u003e\n\u003cp\u003eAn alternative syntax, which conforms to the SQL standard by using the keyword \u003ccode class=\"literal\"\u003eARRAY\u003c/code\u003e, can be used for one-dimensional arrays. \u003ccode class=\"structfield\"\u003epay_by_quarter\u003c/code\u003e could have been defined as:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003e    pay_by_quarter  integer ARRAY[4],\n\u003c/pre\u003e\n\u003cp\u003eOr, if no array size is to be specified:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003e    pay_by_quarter  integer ARRAY,\n\u003c/pre\u003e\n\u003cp\u003eAs before, however, \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e does not enforce the size restriction in any case.\u003c/p\u003e\n\u003c/div\u003e\n\u003cdiv class=\"sect2\" id=\"ARRAYS-INPUT\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3 class=\"title\"\u003e8.15.2. Array Value Input \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eTo write an array value as a literal constant, enclose the element values within curly braces and separate them by commas. (If you know C, this is not unlike the C syntax for initializing structures.) You can put double quotes around any element value, and must do so if it contains commas or curly braces. (More details appear below.) Thus, the general format of an array constant is the following:\u003c/p\u003e\n\u003cpre class=\"synopsis\"\u003e'{ \u003cem class=\"replaceable\"\u003e\u003ccode\u003eval1\u003c/code\u003e\u003c/em\u003e \u003cem class=\"replaceable\"\u003e\u003ccode\u003edelim\u003c/code\u003e\u003c/em\u003e \u003cem class=\"replaceable\"\u003e\u003ccode\u003eval2\u003c/code\u003e\u003c/em\u003e \u003cem class=\"replaceable\"\u003e\u003ccode\u003edelim\u003c/code\u003e\u003c/em\u003e ... }'\n\u003c/pre\u003e\n\u003cp\u003ewhere \u003cem class=\"replaceable\"\u003e\u003ccode\u003edelim\u003c/code\u003e\u003c/em\u003e is the delimiter character for the type, as recorded in its \u003ccode class=\"literal\"\u003epg_type\u003c/code\u003e entry. Among the standard data types provided in the \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e distribution, all use a comma (\u003ccode class=\"literal\"\u003e,\u003c/code\u003e), except for type \u003ccode class=\"type\"\u003ebox\u003c/code\u003e which uses a semicolon (\u003ccode class=\"literal\"\u003e;\u003c/code\u003e). Each \u003cem class=\"replaceable\"\u003e\u003ccode\u003eval\u003c/code\u003e\u003c/em\u003e is either a constant of the array element type, or a subarray. An example of an array constant is:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003e'{{1,2,3},{4,5,6},{7,8,9}}'\n\u003c/pre\u003e\n\u003cp\u003eThis constant is a two-dimensional, 3-by-3 array consisting of three subarrays of integers.\u003c/p\u003e\n\u003cp\u003eTo set an element of an array constant to NULL, write \u003ccode class=\"literal\"\u003eNULL\u003c/code\u003e for the element value. (Any upper- or lower-case variant of \u003ccode class=\"literal\"\u003eNULL\u003c/code\u003e will do.) If you want an actual string value \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003eNULL\u003c/span\u003e”\u003c/span\u003e, you must put double quotes around it.\u003c/p\u003e\n\u003cp\u003e(These kinds of array constants are actually only a special case of the generic type constants discussed in \u003ca class=\"xref\" href=\"/docs/18/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS-GENERIC\" title=\"4.1.2.7. Constants of Other Types\"\u003eSection 4.1.2.7\u003c/a\u003e. The constant is initially treated as a string and passed to the array input conversion routine. An explicit type specification might be necessary.)\u003c/p\u003e\n\u003cp\u003eNow we can show some \u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e statements:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eINSERT INTO sal_emp\n    VALUES ('Bill',\n    '{10000, 10000, 10000, 10000}',\n    '{{\"meeting\", \"lunch\"}, {\"training\", \"presentation\"}}');\n\nINSERT INTO sal_emp\n    VALUES ('Carol',\n    '{20000, 25000, 25000, 25000}',\n    '{{\"breakfast\", \"consulting\"}, {\"meeting\", \"lunch\"}}');\n\u003c/pre\u003e\n\u003cp\u003eThe result of the previous two inserts looks like this:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT * FROM sal_emp;\n name  |      pay_by_quarter       |                 schedule\n-------+---------------------------+-------------------------------------------\n Bill  | {10000,10000,10000,10000} | {{meeting,lunch},{training,presentation}}\n Carol | {20000,25000,25000,25000} | {{breakfast,consulting},{meeting,lunch}}\n(2 rows)\n\u003c/pre\u003e\n\u003cp\u003eMultidimensional arrays must have matching extents for each dimension. A mismatch causes an error, for example:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eINSERT INTO sal_emp\n    VALUES ('Bill',\n    '{10000, 10000, 10000, 10000}',\n    '{{\"meeting\", \"lunch\"}, {\"meeting\"}}');\nERROR:  malformed array literal: \"{{\"meeting\", \"lunch\"}, {\"meeting\"}}\"\nDETAIL:  Multidimensional arrays must have sub-arrays with matching dimensions.\n\u003c/pre\u003e\n\u003cp\u003eThe \u003ccode class=\"literal\"\u003eARRAY\u003c/code\u003e constructor syntax can also be used:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eINSERT INTO sal_emp\n    VALUES ('Bill',\n    ARRAY[10000, 10000, 10000, 10000],\n    ARRAY[['meeting', 'lunch'], ['training', 'presentation']]);\n\nINSERT INTO sal_emp\n    VALUES ('Carol',\n    ARRAY[20000, 25000, 25000, 25000],\n    ARRAY[['breakfast', 'consulting'], ['meeting', 'lunch']]);\n\u003c/pre\u003e\n\u003cp\u003eNotice that the array elements are ordinary SQL constants or expressions; for instance, string literals are single quoted, instead of double quoted as they would be in an array literal. The \u003ccode class=\"literal\"\u003eARRAY\u003c/code\u003e constructor syntax is discussed in more detail in \u003ca class=\"xref\" href=\"/docs/18/sql-expressions.html#SQL-SYNTAX-ARRAY-CONSTRUCTORS\" title=\"4.2.12. Array Constructors\"\u003eSection 4.2.12\u003c/a\u003e.\u003c/p\u003e\n\u003c/div\u003e\n\u003cdiv class=\"sect2\" id=\"ARRAYS-ACCESSING\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3 class=\"title\"\u003e8.15.3. Accessing Arrays \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eNow, we can run some queries on the table. First, we show how to access a single element of an array. This query retrieves the names of the employees whose pay changed in the second quarter:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT name FROM sal_emp WHERE pay_by_quarter[1] \u0026lt;\u0026gt; pay_by_quarter[2];\n\n name\n-------\n Carol\n(1 row)\n\u003c/pre\u003e\n\u003cp\u003eThe array subscript numbers are written within square brackets. By default \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e uses a one-based numbering convention for arrays, that is, an array of \u003cem class=\"replaceable\"\u003e\u003ccode\u003en\u003c/code\u003e\u003c/em\u003e elements starts with \u003ccode class=\"literal\"\u003earray[1]\u003c/code\u003e and ends with \u003ccode class=\"literal\"\u003earray[\u003cem class=\"replaceable\"\u003e\u003ccode\u003en\u003c/code\u003e\u003c/em\u003e]\u003c/code\u003e.\u003c/p\u003e\n\u003cp\u003eThis query retrieves the third quarter pay of all employees:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT pay_by_quarter[3] FROM sal_emp;\n\n pay_by_quarter\n----------------\n          10000\n          25000\n(2 rows)\n\u003c/pre\u003e\n\u003cp\u003eWe can also access arbitrary rectangular slices of an array, or subarrays. An array slice is denoted by writing \u003ccode class=\"literal\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003elower-bound\u003c/code\u003e\u003c/em\u003e:\u003cem class=\"replaceable\"\u003e\u003ccode\u003eupper-bound\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e for one or more array dimensions. For example, this query retrieves the first item on Bill's schedule for the first two days of the week:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT schedule[1:2][1:1] FROM sal_emp WHERE name = 'Bill';\n\n        schedule\n------------------------\n {{meeting},{training}}\n(1 row)\n\u003c/pre\u003e\n\u003cp\u003eIf any dimension is written as a slice, i.e., contains a colon, then all dimensions are treated as slices. Any dimension that has only a single number (no colon) is treated as being from 1 to the number specified. For example, \u003ccode class=\"literal\"\u003e[2]\u003c/code\u003e is treated as \u003ccode class=\"literal\"\u003e[1:2]\u003c/code\u003e, as in this example:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT schedule[1:2][2] FROM sal_emp WHERE name = 'Bill';\n\n                 schedule\n-------------------------------------------\n {{meeting,lunch},{training,presentation}}\n(1 row)\n\u003c/pre\u003e\n\u003cp\u003eTo avoid confusion with the non-slice case, it's best to use slice syntax for all dimensions, e.g., \u003ccode class=\"literal\"\u003e[1:2][1:1]\u003c/code\u003e, not \u003ccode class=\"literal\"\u003e[2][1:1]\u003c/code\u003e.\u003c/p\u003e\n\u003cp\u003eIt is possible to omit the \u003cem class=\"replaceable\"\u003e\u003ccode\u003elower-bound\u003c/code\u003e\u003c/em\u003e and/or \u003cem class=\"replaceable\"\u003e\u003ccode\u003eupper-bound\u003c/code\u003e\u003c/em\u003e of a slice specifier; the missing bound is replaced by the lower or upper limit of the array's subscripts. For example:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT schedule[:2][2:] FROM sal_emp WHERE name = 'Bill';\n\n        schedule\n------------------------\n {{lunch},{presentation}}\n(1 row)\n\nSELECT schedule[:][1:1] FROM sal_emp WHERE name = 'Bill';\n\n        schedule\n------------------------\n {{meeting},{training}}\n(1 row)\n\u003c/pre\u003e\n\u003cp\u003eAn 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 \u003ccode class=\"literal\"\u003eschedule\u003c/code\u003e currently has the dimensions \u003ccode class=\"literal\"\u003e[1:3][1:2]\u003c/code\u003e then referencing \u003ccode class=\"literal\"\u003eschedule[3][3]\u003c/code\u003e yields NULL. Similarly, an array reference with the wrong number of subscripts yields a null rather than an error.\u003c/p\u003e\n\u003cp\u003eAn array slice expression likewise yields null if the array itself or any of the subscript expressions are null. However, in other 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. (This does not match non-slice behavior and is done for historical reasons.) If the requested slice partially overlaps the array bounds, then it is silently reduced to just the overlapping region instead of returning null.\u003c/p\u003e\n\u003cp\u003eThe current dimensions of any array value can be retrieved with the \u003ccode class=\"function\"\u003earray_dims\u003c/code\u003e function:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT array_dims(schedule) FROM sal_emp WHERE name = 'Carol';\n\n array_dims\n------------\n [1:2][1:2]\n(1 row)\n\u003c/pre\u003e\n\u003cp\u003e\u003ccode class=\"function\"\u003earray_dims\u003c/code\u003e produces a \u003ccode class=\"type\"\u003etext\u003c/code\u003e result, which is convenient for people to read but perhaps inconvenient for programs. Dimensions can also be retrieved with \u003ccode class=\"function\"\u003earray_upper\u003c/code\u003e and \u003ccode class=\"function\"\u003earray_lower\u003c/code\u003e, which return the upper and lower bound of a specified array dimension, respectively:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT array_upper(schedule, 1) FROM sal_emp WHERE name = 'Carol';\n\n array_upper\n-------------\n           2\n(1 row)\n\u003c/pre\u003e\n\u003cp\u003e\u003ccode class=\"function\"\u003earray_length\u003c/code\u003e will return the length of a specified array dimension:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT array_length(schedule, 1) FROM sal_emp WHERE name = 'Carol';\n\n array_length\n--------------\n            2\n(1 row)\n\u003c/pre\u003e\n\u003cp\u003e\u003ccode class=\"function\"\u003ecardinality\u003c/code\u003e returns the total number of elements in an array across all dimensions. It is effectively the number of rows a call to \u003ccode class=\"function\"\u003eunnest\u003c/code\u003e would yield:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT cardinality(schedule) FROM sal_emp WHERE name = 'Carol';\n\n cardinality\n-------------\n           4\n(1 row)\n\u003c/pre\u003e\n\u003c/div\u003e\n\u003cdiv class=\"sect2\" id=\"ARRAYS-MODIFYING\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3 class=\"title\"\u003e8.15.4. Modifying Arrays \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eAn array value can be replaced completely:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eUPDATE sal_emp SET pay_by_quarter = '{25000,25000,27000,27000}'\n    WHERE name = 'Carol';\n\u003c/pre\u003e\n\u003cp\u003eor using the \u003ccode class=\"literal\"\u003eARRAY\u003c/code\u003e expression syntax:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eUPDATE sal_emp SET pay_by_quarter = ARRAY[25000,25000,27000,27000]\n    WHERE name = 'Carol';\n\u003c/pre\u003e\n\u003cp\u003eAn array can also be updated at a single element:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eUPDATE sal_emp SET pay_by_quarter[4] = 15000\n    WHERE name = 'Bill';\n\u003c/pre\u003e\n\u003cp\u003eor updated in a slice:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eUPDATE sal_emp SET pay_by_quarter[1:2] = '{27000,27000}'\n    WHERE name = 'Carol';\n\u003c/pre\u003e\n\u003cp\u003eThe slice syntaxes with omitted \u003cem class=\"replaceable\"\u003e\u003ccode\u003elower-bound\u003c/code\u003e\u003c/em\u003e and/or \u003cem class=\"replaceable\"\u003e\u003ccode\u003eupper-bound\u003c/code\u003e\u003c/em\u003e can be used too, but only when updating an array value that is not NULL or zero-dimensional (otherwise, there is no existing subscript limit to substitute).\u003c/p\u003e\n\u003cp\u003eA stored array value can be enlarged by assigning to elements not already present. Any positions between those previously present and the newly assigned elements will be filled with nulls. For example, if array \u003ccode class=\"literal\"\u003emyarray\u003c/code\u003e currently has 4 elements, it will have six elements after an update that assigns to \u003ccode class=\"literal\"\u003emyarray[6]\u003c/code\u003e; \u003ccode class=\"literal\"\u003emyarray[5]\u003c/code\u003e will contain null. Currently, enlargement in this fashion is only allowed for one-dimensional arrays, not multidimensional arrays.\u003c/p\u003e\n\u003cp\u003eSubscripted assignment allows creation of arrays that do not use one-based subscripts. For example one might assign to \u003ccode class=\"literal\"\u003emyarray[-2:7]\u003c/code\u003e to create an array with subscript values from -2 to 7.\u003c/p\u003e\n\u003cp\u003eNew array values can also be constructed using the concatenation operator, \u003ccode class=\"literal\"\u003e||\u003c/code\u003e:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT ARRAY[1,2] || ARRAY[3,4];\n ?column?\n-----------\n {1,2,3,4}\n(1 row)\n\nSELECT ARRAY[5,6] || ARRAY[[1,2],[3,4]];\n      ?column?\n---------------------\n {{5,6},{1,2},{3,4}}\n(1 row)\n\u003c/pre\u003e\n\u003cp\u003eThe concatenation operator allows a single element to be pushed onto the beginning or end of a one-dimensional array. It also accepts two \u003cem class=\"replaceable\"\u003e\u003ccode\u003eN\u003c/code\u003e\u003c/em\u003e-dimensional arrays, or an \u003cem class=\"replaceable\"\u003e\u003ccode\u003eN\u003c/code\u003e\u003c/em\u003e-dimensional and an \u003cem class=\"replaceable\"\u003e\u003ccode\u003eN+1\u003c/code\u003e\u003c/em\u003e-dimensional array.\u003c/p\u003e\n\u003cp\u003eWhen a single element is pushed onto either the beginning or end of a one-dimensional array, the result is an array with the same lower bound subscript as the array operand. For example:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT array_dims(1 || '[0:1]={2,3}'::int[]);\n array_dims\n------------\n [0:2]\n(1 row)\n\nSELECT array_dims(ARRAY[1,2] || 3);\n array_dims\n------------\n [1:3]\n(1 row)\n\u003c/pre\u003e\n\u003cp\u003eWhen two arrays with an equal number of dimensions are concatenated, the result retains the lower bound subscript of the left-hand operand's outer dimension. The result is an array comprising every element of the left-hand operand followed by every element of the right-hand operand. For example:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT array_dims(ARRAY[1,2] || ARRAY[3,4,5]);\n array_dims\n------------\n [1:5]\n(1 row)\n\nSELECT array_dims(ARRAY[[1,2],[3,4]] || ARRAY[[5,6],[7,8],[9,0]]);\n array_dims\n------------\n [1:5][1:2]\n(1 row)\n\u003c/pre\u003e\n\u003cp\u003eWhen an \u003cem class=\"replaceable\"\u003e\u003ccode\u003eN\u003c/code\u003e\u003c/em\u003e-dimensional array is pushed onto the beginning or end of an \u003cem class=\"replaceable\"\u003e\u003ccode\u003eN+1\u003c/code\u003e\u003c/em\u003e-dimensional array, the result is analogous to the element-array case above. Each \u003cem class=\"replaceable\"\u003e\u003ccode\u003eN\u003c/code\u003e\u003c/em\u003e-dimensional sub-array is essentially an element of the \u003cem class=\"replaceable\"\u003e\u003ccode\u003eN+1\u003c/code\u003e\u003c/em\u003e-dimensional array's outer dimension. For example:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT array_dims(ARRAY[1,2] || ARRAY[[3,4],[5,6]]);\n array_dims\n------------\n [1:3][1:2]\n(1 row)\n\u003c/pre\u003e\n\u003cp\u003eAn array can also be constructed by using the functions \u003ccode class=\"function\"\u003earray_prepend\u003c/code\u003e, \u003ccode class=\"function\"\u003earray_append\u003c/code\u003e, or \u003ccode class=\"function\"\u003earray_cat\u003c/code\u003e. The first two only support one-dimensional arrays, but \u003ccode class=\"function\"\u003earray_cat\u003c/code\u003e supports multidimensional arrays. Some examples:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT array_prepend(1, ARRAY[2,3]);\n array_prepend\n---------------\n {1,2,3}\n(1 row)\n\nSELECT array_append(ARRAY[1,2], 3);\n array_append\n--------------\n {1,2,3}\n(1 row)\n\nSELECT array_cat(ARRAY[1,2], ARRAY[3,4]);\n array_cat\n-----------\n {1,2,3,4}\n(1 row)\n\nSELECT array_cat(ARRAY[[1,2],[3,4]], ARRAY[5,6]);\n      array_cat\n---------------------\n {{1,2},{3,4},{5,6}}\n(1 row)\n\nSELECT array_cat(ARRAY[5,6], ARRAY[[1,2],[3,4]]);\n      array_cat\n---------------------\n {{5,6},{1,2},{3,4}}\n\u003c/pre\u003e\n\u003cp\u003eIn simple cases, the concatenation operator discussed above is preferred over direct use of these functions. However, because the concatenation operator is overloaded to serve all three cases, there are situations where use of one of the functions is helpful to avoid ambiguity. For example consider:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT ARRAY[1, 2] || '{3, 4}';  -- the untyped literal is taken as an array\n ?column?\n-----------\n {1,2,3,4}\n\nSELECT ARRAY[1, 2] || '7';                 -- so is this one\nERROR:  malformed array literal: \"7\"\n\nSELECT ARRAY[1, 2] || NULL;                -- so is an undecorated NULL\n ?column?\n----------\n {1,2}\n(1 row)\n\nSELECT array_append(ARRAY[1, 2], NULL);    -- this might have been meant\n array_append\n--------------\n {1,2,NULL}\n\u003c/pre\u003e\n\u003cp\u003eIn the examples above, the parser sees an integer array on one side of the concatenation operator, and a constant of undetermined type on the other. The heuristic it uses to resolve the constant's type is to assume it's of the same type as the operator's other input — in this case, integer array. So the concatenation operator is presumed to represent \u003ccode class=\"function\"\u003earray_cat\u003c/code\u003e, not \u003ccode class=\"function\"\u003earray_append\u003c/code\u003e. When that's the wrong choice, it could be fixed by casting the constant to the array's element type; but explicit use of \u003ccode class=\"function\"\u003earray_append\u003c/code\u003e might be a preferable solution.\u003c/p\u003e\n\u003c/div\u003e\n\u003cdiv class=\"sect2\" id=\"ARRAYS-SEARCHING\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3 class=\"title\"\u003e8.15.5. Searching in Arrays \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eTo search for a value in an array, each value must be checked. This can be done manually, if you know the size of the array. For example:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT * FROM sal_emp WHERE pay_by_quarter[1] = 10000 OR\n                            pay_by_quarter[2] = 10000 OR\n                            pay_by_quarter[3] = 10000 OR\n                            pay_by_quarter[4] = 10000;\n\u003c/pre\u003e\n\u003cp\u003eHowever, this quickly becomes tedious for large arrays, and is not helpful if the size of the array is unknown. An alternative method is described in \u003ca class=\"xref\" href=\"/docs/18/functions-comparisons.html\" title=\"9.25. Row and Array Comparisons\"\u003eSection 9.25\u003c/a\u003e. The above query could be replaced by:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT * FROM sal_emp WHERE 10000 = ANY (pay_by_quarter);\n\u003c/pre\u003e\n\u003cp\u003eIn addition, you can find rows where the array has all values equal to 10000 with:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter);\n\u003c/pre\u003e\n\u003cp\u003eAlternatively, the \u003ccode class=\"function\"\u003egenerate_subscripts\u003c/code\u003e function can be used. For example:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT * FROM\n   (SELECT pay_by_quarter,\n           generate_subscripts(pay_by_quarter, 1) AS s\n      FROM sal_emp) AS foo\n WHERE pay_by_quarter[s] = 10000;\n\u003c/pre\u003e\n\u003cp\u003eThis function is described in \u003ca class=\"xref\" href=\"/docs/18/functions-srf.html#FUNCTIONS-SRF-SUBSCRIPTS\" title=\"Table 9.70. Subscript Generating Functions\"\u003eTable 9.70\u003c/a\u003e.\u003c/p\u003e\n\u003cp\u003eYou can also search an array using the \u003ccode class=\"literal\"\u003e\u0026amp;\u0026amp;\u003c/code\u003e operator, which checks whether the left operand overlaps with the right operand. For instance:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT * FROM sal_emp WHERE pay_by_quarter \u0026amp;\u0026amp; ARRAY[10000];\n\u003c/pre\u003e\n\u003cp\u003eThis and other array operators are further described in \u003ca class=\"xref\" href=\"/docs/18/functions-array.html\" title=\"9.19. Array Functions and Operators\"\u003eSection 9.19\u003c/a\u003e. It can be accelerated by an appropriate index, as described in \u003ca class=\"xref\" href=\"/docs/18/indexes-types.html\" title=\"11.2. Index Types\"\u003eSection 11.2\u003c/a\u003e.\u003c/p\u003e\n\u003cp\u003eYou can also search for specific values in an array using the \u003ccode class=\"function\"\u003earray_position\u003c/code\u003e and \u003ccode class=\"function\"\u003earray_positions\u003c/code\u003e functions. The former returns the subscript of the first occurrence of a value in an array; the latter returns an array with the subscripts of all occurrences of the value in the array. For example:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT array_position(ARRAY['sun','mon','tue','wed','thu','fri','sat'], 'mon');\n array_position\n----------------\n              2\n(1 row)\n\nSELECT array_positions(ARRAY[1, 4, 3, 1, 3, 4, 2, 1], 1);\n array_positions\n-----------------\n {1,4,8}\n(1 row)\n\u003c/pre\u003e\n\u003cdiv class=\"tip\"\u003e\n\u003ch3 class=\"title\"\u003eTip\u003c/h3\u003e\n\u003cp\u003eArrays are not sets; searching for specific array elements can be a sign of database misdesign. Consider using a separate table with a row for each item that would be an array element. This will be easier to search, and is likely to scale better for a large number of elements.\u003c/p\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cdiv class=\"sect2\" id=\"ARRAYS-IO\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3 class=\"title\"\u003e8.15.6. Array Input and Output Syntax \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eThe external text representation of an array value consists of items that are interpreted according to the I/O conversion rules for the array's element type, plus decoration that indicates the array structure. The decoration consists of curly braces (\u003ccode class=\"literal\"\u003e{\u003c/code\u003e and \u003ccode class=\"literal\"\u003e}\u003c/code\u003e) around the array value plus delimiter characters between adjacent items. The delimiter character is usually a comma (\u003ccode class=\"literal\"\u003e,\u003c/code\u003e) but can be something else: it is determined by the \u003ccode class=\"literal\"\u003etypdelim\u003c/code\u003e setting for the array's element type. Among the standard data types provided in the \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e distribution, all use a comma, except for type \u003ccode class=\"type\"\u003ebox\u003c/code\u003e, which uses a semicolon (\u003ccode class=\"literal\"\u003e;\u003c/code\u003e). In a multidimensional array, each dimension (row, plane, cube, etc.) gets its own level of curly braces, and delimiters must be written between adjacent curly-braced entities of the same level.\u003c/p\u003e\n\u003cp\u003eThe array output routine will put double quotes around element values if they are empty strings, contain curly braces, delimiter characters, double quotes, backslashes, or white space, or match the word \u003ccode class=\"literal\"\u003eNULL\u003c/code\u003e. 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 the presence or absence of quotes.\u003c/p\u003e\n\u003cp\u003eBy default, the lower bound index value of an array's dimensions is 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 (\u003ccode class=\"literal\"\u003e[]\u003c/code\u003e) around each array dimension's lower and upper bounds, with a colon (\u003ccode class=\"literal\"\u003e:\u003c/code\u003e) delimiter character in between. The array dimension decoration is followed by an equal sign (\u003ccode class=\"literal\"\u003e=\u003c/code\u003e). For example:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT f1[1][-2][3] AS e1, f1[1][-1][5] AS e2\n FROM (SELECT '[1:1][-2:-1][3:5]={{{1,2,3},{4,5,6}}}'::int[] AS f1) AS ss;\n\n e1 | e2\n----+----\n  1 |  6\n(1 row)\n\u003c/pre\u003e\n\u003cp\u003eThe array output routine will include explicit dimensions in its result only when there are one or more lower bounds different from one.\u003c/p\u003e\n\u003cp\u003eIf the value written for an element is \u003ccode class=\"literal\"\u003eNULL\u003c/code\u003e (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 \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003eNULL\u003c/span\u003e”\u003c/span\u003e to be entered. Also, for backward compatibility with pre-8.2 versions of \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e, the \u003ca class=\"xref\" href=\"/docs/18/runtime-config-compatible.html#GUC-ARRAY-NULLS\"\u003earray_nulls\u003c/a\u003e configuration parameter can be turned \u003ccode class=\"literal\"\u003eoff\u003c/code\u003e to suppress recognition of \u003ccode class=\"literal\"\u003eNULL\u003c/code\u003e as a NULL.\u003c/p\u003e\n\u003cp\u003eAs shown previously, when writing an array value you can use double quotes around any individual array element. You \u003cspan class=\"emphasis\"\u003e\u003cem\u003emust\u003c/em\u003e\u003c/span\u003e do so if the element value would otherwise confuse the array-value parser. For example, elements containing curly braces, commas (or the data type's delimiter character), double quotes, backslashes, or leading or trailing whitespace must be double-quoted. Empty strings and strings matching the word \u003ccode class=\"literal\"\u003eNULL\u003c/code\u003e 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 avoid quotes and use backslash-escaping to protect all data characters that would otherwise be taken as array syntax.\u003c/p\u003e\n\u003cp\u003eYou can add whitespace before a left brace or after a right brace. You can also add whitespace before or after any individual item string. In all of these cases the whitespace will be ignored. However, whitespace within double-quoted elements, or surrounded on both sides by non-whitespace characters of an element, is not ignored.\u003c/p\u003e\n\u003cdiv class=\"tip\"\u003e\n\u003ch3 class=\"title\"\u003eTip\u003c/h3\u003e\n\u003cp\u003eThe \u003ccode class=\"literal\"\u003eARRAY\u003c/code\u003e constructor syntax (see \u003ca class=\"xref\" href=\"/docs/18/sql-expressions.html#SQL-SYNTAX-ARRAY-CONSTRUCTORS\" title=\"4.2.12. Array Constructors\"\u003eSection 4.2.12\u003c/a\u003e) is often easier to work with than the array-literal syntax when writing array values in SQL commands. In \u003ccode class=\"literal\"\u003eARRAY\u003c/code\u003e, individual element values are written the same way they would be written when not members of an array.\u003c/p\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e","related":[],"sections":[]}},"RequestedLocale":"zh-Hans","Fallback":true,"Versions":["10","11","12","13","14","15","16","17","18","19","20"],"Locales":["en"],"Signatures":null,"Spellings":null,"SQLState":null,"Evidence":null}