Chapter 6. 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 their use, 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 query will create a table named sal_emp with columns including a text string (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 do some INSERTs. Observe that 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"}}');

Now, we can run some queries on sal_emp. 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 subscript : upper subscript for one or more array dimensions. 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 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.

An array value can be replaced completely:

UPDATE sal_emp SET pay_by_quarter = '{25000,25000,27000,27000}'
    WHERE name = 'Carol';

or 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';

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.

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.

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.

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):

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 part of the primary PostgreSQL distribution, 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 columns), 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.

Tip: 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.

Note: A limitation of the present array implementation is that individual elements of an array cannot be SQL NULLs. The entire array can be set to NULL, but you can't have an array with some elements NULL and some not. Fixing this is on the to-do list.

Array input and output syntax. 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 datatypes 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.

Quoting array elements. 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 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 datatypes it is safe to assume that double quotes will never appear, but for textual datatypes 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.)

Tip: Remember that what you write in an SQL query 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 query to get one backslash into the stored array element.)