arrays PostgreSQL allows columns of a table to be defined as variable-length multidimensional arrays. Arrays of any built-in type or user-defined type can be created. To illustrate the use of array types, we create this table: CREATE TABLE sal_emp ( name text, pay_by_quarter integer[], schedule text[][] ); As shown, an array data type is named by appending square brackets ([]) to the data type name of the array elements. The above command will create a table named sal_emp with a column of type text (name), a one-dimensional array of type integer (pay_by_quarter), which represents the employee's salary by quarter, and a two-dimensional array of text (schedule), which represents the employee's weekly schedule. Now we can show some INSERT statements. To write an array value, we enclose the element values within curly braces and separate them by commas. If you know C, this is not unlike the syntax for initializing structures. (More details appear below.) INSERT INTO sal_emp VALUES ('Bill', '{10000, 10000, 10000, 10000}', '{{"meeting", "lunch"}, {}}'); INSERT INTO sal_emp VALUES ('Carol', '{20000, 25000, 25000, 25000}', '{{"talk", "consult"}, {"meeting"}}'); A limitation of the present array implementation is that individual elements of an array cannot be SQL null values. The entire array can be set to null, but you can't have an array with some elements null and some not. This can lead to surprising results. For example, the result of the previous two inserts looks like this: SELECT * FROM sal_emp; name | pay_by_quarter | schedule -------+---------------------------+-------------------- Bill | {10000,10000,10000,10000} | {{meeting},{""}} Carol | {20000,25000,25000,25000} | {{talk},{meeting}} (2 rows) Because the [2][2] element of schedule is missing in each of the INSERT statements, the [1][2] element is discarded. Fixing this is on the to-do list. The ARRAY expression syntax may also be used: INSERT INTO sal_emp VALUES ('Bill', ARRAY[10000, 10000, 10000, 10000], ARRAY[['meeting', 'lunch'], ['','']]); INSERT INTO sal_emp VALUES ('Carol', ARRAY[20000, 25000, 25000, 25000], ARRAY[['talk', 'consult'], ['meeting', '']]); SELECT * FROM sal_emp; name | pay_by_quarter | schedule -------+---------------------------+------------------------------- Bill | {10000,10000,10000,10000} | {{meeting,lunch},{"",""}} Carol | {20000,25000,25000,25000} | {{talk,consult},{meeting,""}} (2 rows) Note that with this syntax, multidimensional arrays must have matching extents for each dimension. This eliminates the missing-array-elements problem above. For example: INSERT INTO sal_emp VALUES ('Carol', ARRAY[20000, 25000, 25000, 25000], ARRAY[['talk', 'consult'], ['meeting']]); ERROR: Multidimensional arrays must have array expressions with matching dimensions Also notice that string literals are single quoted instead of double quoted. The examples in the rest of this section are based on the ARRAY expression syntax INSERTs. Now, we can run some queries on the table. First, we show how to access a single element of an array at a time. This query retrieves the names of the employees whose pay changed in the second quarter: SELECT name FROM sal_emp WHERE pay_by_quarter[1] <> pay_by_quarter[2]; name ------- Carol (1 row) The array subscript numbers are written within square brackets. By default PostgreSQL uses the one-based numbering convention for arrays, that is, an array of n elements starts with array[1] and ends with array[n]. This query retrieves the third quarter pay of all employees: SELECT pay_by_quarter[3] FROM sal_emp; pay_by_quarter ---------------- 10000 25000 (2 rows) We can also access arbitrary rectangular slices of an array, or subarrays. An array slice is denoted by writing lower-bound:upper-bound 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: SELECT schedule[1:2][1:1] FROM sal_emp WHERE name = 'Bill'; schedule -------------------- {{meeting},{""}} (1 row) We could also have written SELECT schedule[1:2][1] FROM sal_emp WHERE name = 'Bill'; with the same result. An array subscripting operation is always taken to represent an array slice if any of the subscripts are written in the form lower:upper. A lower bound of 1 is assumed for any subscript where only one value is specified. Another example follows: SELECT schedule[1:2][2] FROM sal_emp WHERE name = 'Bill'; schedule --------------------------- {{meeting,lunch},{"",""}} (1 row) Additionally, we can also access a single arbitrary array element of a one-dimensional array with the array_subscript function: SELECT array_subscript(pay_by_quarter, 2) FROM sal_emp WHERE name = 'Bill'; array_subscript ----------------- 10000 (1 row) An array value can be replaced completely: UPDATE sal_emp SET pay_by_quarter = '{25000,25000,27000,27000}' WHERE name = 'Carol'; or using the ARRAY expression syntax: UPDATE sal_emp SET pay_by_quarter = ARRAY[25000,25000,27000,27000] WHERE name = 'Carol'; Anywhere you can use the curly braces array syntax, you can also use the ARRAY expression syntax. The remainder of this section will illustrate only one or the other, but not both. An array may also be updated at a single element: UPDATE sal_emp SET pay_by_quarter[4] = 15000 WHERE name = 'Bill'; or updated in a slice: UPDATE sal_emp SET pay_by_quarter[1:2] = '{27000,27000}' WHERE name = 'Carol'; A one-dimensional array may also be updated with the array_assign function: UPDATE sal_emp SET pay_by_quarter = array_assign(pay_by_quarter, 4, 15000) WHERE name = 'Bill'; An array can be enlarged by assigning to an element adjacent to those already present, or by assigning to a slice that is adjacent to or overlaps the data already present. For example, if an array value currently has 4 elements, it will have five elements after an update that assigns to array[5]. Currently, enlargement in this fashion is only allowed for one-dimensional arrays, not multidimensional arrays. Array slice assignment allows creation of arrays that do not use one-based subscripts. For example one might assign to array[-2:7] to create an array with subscript values running from -2 to 7. An array can also be enlarged by using the concatenation operator, ||. SELECT ARRAY[1,2] || ARRAY[3,4]; ?column? --------------- {{1,2},{3,4}} (1 row) SELECT ARRAY[5,6] || ARRAY[[1,2],[3,4]]; ?column? --------------------- {{5,6},{1,2},{3,4}} (1 row) The concatenation operator allows a single element to be pushed on to the beginning or end of a one-dimensional array. It also allows two N-dimensional arrays, or an N-dimensional and an N+1-dimensional array. In the former case, the two N-dimension arrays become outer elements of an N+1-dimensional array. In the latter, the N-dimensional array is added as either the first or last outer element of the N+1-dimensional array. The array is extended in the direction of the push. Hence, by pushing onto the beginning of an array with a one-based subscript, a zero-based subscript array is created: SELECT array_dims(t.f) FROM (SELECT 1 || ARRAY[2,3] AS f) AS t; array_dims ------------ [0:2] (1 row) An array can also be enlarged by using the functions array_prepend, array_append, or array_cat. The first two only support one-dimensional arrays, but array_cat supports multidimensional arrays. Note that the concatenation operator discussed above is preferred over direct use of these functions. In fact, the functions are primarily for use in implementing the concatenation operator. However, they may be directly useful in the creation of user-defined aggregates. Some examples: SELECT array_prepend(1, ARRAY[2,3]); array_prepend --------------- {1,2,3} (1 row) SELECT array_append(ARRAY[1,2], 3); array_append -------------- {1,2,3} (1 row) SELECT array_cat(ARRAY[1,2], ARRAY[3,4]); array_cat --------------- {{1,2},{3,4}} (1 row) SELECT array_cat(ARRAY[[1,2],[3,4]], ARRAY[5,6]); array_cat --------------------- {{1,2},{3,4},{5,6}} (1 row) SELECT array_cat(ARRAY[5,6], ARRAY[[1,2],[3,4]]); array_cat --------------------- {{5,6},{1,2},{3,4}} The syntax for CREATE TABLE allows fixed-length arrays to be defined: CREATE TABLE tictactoe ( squares integer[3][3] ); However, the current implementation does not enforce the array size limits --- the behavior is the same as for arrays of unspecified length. An alternative syntax for one-dimensional arrays may be used. pay_by_quarter could have been defined as: pay_by_quarter integer ARRAY[4], This syntax may only be used with the integer constant to denote the array size. Actually, the 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 number of dimensions or sizes in CREATE TABLE is simply documentation, it does not affect runtime behavior. The current dimensions of any array value can be retrieved with the array_dims function: SELECT array_dims(schedule) FROM sal_emp WHERE name = 'Carol'; array_dims ------------ [1:2][1:1] (1 row) array_dims produces a text result, which is convenient for people to read but perhaps not so convenient for programs. array_upper and array_lower return the upper/lower bound of the given array dimension, respectively. To search for a value in an array, you must check each value of the array. This can be done by hand (if you know the size of the array). For example: SELECT * FROM sal_emp WHERE pay_by_quarter[1] = 10000 OR pay_by_quarter[2] = 10000 OR pay_by_quarter[3] = 10000 OR pay_by_quarter[4] = 10000; However, this quickly becomes tedious for large arrays, and is not helpful if the size of the array is unknown. Although it is not built into PostgreSQL, there is an extension available that defines new functions and operators for iterating over array values. Using this, the above query could be: SELECT * FROM sal_emp WHERE pay_by_quarter[1:4] *= 10000; To search the entire array (not just specified slices), you could use: SELECT * FROM sal_emp WHERE pay_by_quarter *= 10000; In addition, you could find rows where the array had all values equal to 10 000 with: SELECT * FROM sal_emp WHERE pay_by_quarter **= 10000; To install this optional module, look in the contrib/array directory of the PostgreSQL source distribution. Arrays are not sets; using arrays in the manner described in the previous paragraph is often a sign of database misdesign. The array field should generally be split off into a separate table. Tables can obviously be searched easily. The external 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 ({ and }) around the array value plus delimiter characters between adjacent items. The delimiter character is usually a comma (,) but can be something else: it is determined by the typdelim setting for the array's element type. (Among the standard data types provided in the PostgreSQL distribution, type box uses a semicolon (;) but all the others use comma.) 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. You may write whitespace before a left brace, after a right brace, or before any individual item string. Whitespace after an item is not ignored, however: after skipping leading whitespace, everything up to the next right brace or delimiter is taken as the item value. As illustrated earlier in this chapter, arrays may also be represented using the ARRAY expression syntax. This 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 the keyword ARRAY and square brackets ([ and ]) around the array values, plus delimiter characters between adjacent items. The delimiter character is always a comma (,). When representing multidimensional arrays, the keyword ARRAY is only necessary for the outer level. For example, '{{"hello world", "happy birthday"}}' could be written as: SELECT ARRAY[['hello world', 'happy birthday']]; array ------------------------------------ {{"hello world","happy birthday"}} (1 row) or it also could be written as: SELECT ARRAY[ARRAY['hello world', 'happy birthday']]; array ------------------------------------ {{"hello world","happy birthday"}} (1 row) A final method to represent an array, is through an ARRAY sub-select expression. For example: SELECT ARRAY(SELECT oid FROM pg_proc WHERE proname LIKE 'bytea%'); ?column? ------------------------------------------------------------- {2011,1954,1948,1952,1951,1244,1950,2005,1949,1953,2006,31} (1 row) The sub-select may only return a single column. The resulting one-dimensional array will have an element for each row in the sub-select result, with an element type matching that of the sub-select's target column. Arrays may be cast from one type to another in similar fashion to other data types: SELECT ARRAY[1,2,3]::oid[]; array --------- {1,2,3} (1 row) SELECT CAST(ARRAY[1,2,3] AS float8[]); array --------- {1,2,3} (1 row) As shown above, when writing an array value you may write double quotes around any individual array element. You must do so if the element value would otherwise confuse the array-value parser. For example, elements containing curly braces, commas (or whatever the delimiter character is), double quotes, backslashes, or leading white space must be double-quoted. To put a double quote or backslash in an array element value, precede it with a backslash. Alternatively, you can use backslash-escaping to protect all data characters that would otherwise be taken as array syntax or ignorable white space. The discussion in the preceding paragraph with respect to double quoting does not pertain to the ARRAY expression syntax. In that case, each element is quoted exactly as any other literal value of the element type. The array output routine will put double quotes around element values if they are empty strings or contain curly braces, delimiter characters, double quotes, backslashes, or white space. Double quotes and backslashes embedded in element values will be backslash-escaped. For numeric data types it is safe to assume that double quotes will never appear, but for textual data types one should be prepared to cope with either presence or absence of quotes. (This is a change in behavior from pre-7.2 PostgreSQL releases.) Remember that what you write in an SQL command will first be interpreted as a string literal, and then as an array. This doubles the number of backslashes you need. For example, to insert a text array value containing a backslash and a double quote, you'd need to write INSERT ... VALUES ('{"\\\\","\\""}'); The string-literal processor removes one level of backslashes, so that what arrives at the array-value parser looks like {"\\","\""}. In turn, the strings fed to the text data type's input routine become \ and " respectively. (If we were working with a data type whose input routine also treated backslashes specially, bytea for example, we might need as many as eight backslashes in the command to get one backslash into the stored array element.)