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.

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.

The syntax for CREATE TABLE allows
the exact size of arrays to be specified, for example:

CREATE TABLE tictactoe (
squares integer[3][3]
);

However, the current implementation ignores any supplied
array size limits, i.e., the behavior is the same as for arrays
of unspecified length.

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 the array size or
number of dimensions in CREATE TABLE
is simply documentation; it does not affect run-time
behavior.

An alternative syntax, which conforms to the SQL standard by
using the keyword ARRAY, can be used
for one-dimensional arrays. pay_by_quarter could have been defined
as:

pay_by_quarter integer ARRAY[4],

Or, if no array size is to be specified:

pay_by_quarter integer ARRAY,

As before, however, PostgreSQL does not enforce the size
restriction in any case.

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

'{ val1delimval2delim ... }'

where delim is the delimiter
character for the type, as recorded in its pg_type entry. Among the standard data types
provided in the PostgreSQL
distribution, all use a comma (,),
except for type box which uses a
semicolon (;). Each val is either a constant of the array
element type, or a subarray. An example of an array constant
is:

'{{1,2,3},{4,5,6},{7,8,9}}'

This constant is a two-dimensional, 3-by-3 array consisting
of three subarrays of integers.

To set an element of an array constant to NULL, write
NULL for the element value. (Any
upper- or lower-case variant of NULL
will do.) If you want an actual string value "NULL", you must put double quotes around
it.

(These kinds of array constants are actually only a special
case of the generic type constants discussed in Section
4.1.2.7. The constant is initially treated as a string and
passed to the array input conversion routine. An explicit type
specification might be necessary.)

Notice 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 ARRAY constructor syntax is
discussed in more detail in Section
4.2.11.

Now, 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:

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 a one-based numbering
convention for arrays, that is, an array of n elements starts with array[1] and ends with array[n].

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:

If 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, [2] is treated as [1:2], as in this example:

To avoid confusion with the non-slice case, it's best to use
slice syntax for all dimensions, e.g., [1:2][1:1], not [2][1:1].

An array subscript expression will return null if either the
array itself or any of the subscript expressions are null.
Also, null is returned if a subscript is outside the array
bounds (this case does not raise an error). For example, if
schedule currently has the dimensions
[1:3][1:2] then referencing schedule[3][3] yields NULL. Similarly, an array
reference with the wrong number of subscripts yields a null
rather than an error.

An 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.

The current dimensions of any array value can be retrieved
with the array_dims function:

array_dims produces a
text result, which is convenient for
people to read but perhaps inconvenient for programs.
Dimensions can also be retrieved with array_upper and array_lower, which return the upper and lower
bound of a specified array dimension, respectively:

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

An array can 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 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 myarray currently has 4 elements, it will have
six elements after an update that assigns to myarray[6]; myarray[5]
will contain null. Currently, enlargement in this fashion is
only allowed for one-dimensional arrays, not multidimensional
arrays.

Subscripted assignment allows creation of arrays that do not
use one-based subscripts. For example one might assign to
myarray[-2:7] to create an array with
subscript values from -2 to 7.

New array values can also be constructed using the
concatenation operator, ||:

The concatenation operator allows a single element to be
pushed onto the beginning or end of a one-dimensional array. It
also accepts two N-dimensional
arrays, or an N-dimensional and
an N+1-dimensional array.

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

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

When an N-dimensional array
is pushed onto the beginning or end of an N+1-dimensional array, the result is
analogous to the element-array case above. Each N-dimensional sub-array is essentially an
element of the N+1-dimensional
array's outer dimension. For example:

An array can also be constructed 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, these
functions primarily exist for use in implementing the
concatenation operator. However, they might be directly useful
in the creation of user-defined aggregates. Some examples:

However, 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 Section 9.21. The above query
could be replaced by:

SELECT * FROM sal_emp WHERE 10000 = ANY (pay_by_quarter);

In addition, you can find rows where the array has all
values equal to 10000 with:

SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter);

Alternatively, the generate_subscripts function can be used. For
example:

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

The 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 ({ 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, all use a comma,
except for type box, which uses a
semicolon (;). 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.

The 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 NULL. Double
quotes and backslashes embedded in element values will be
backslash-escaped. For numeric data types it is safe to assume
that double quotes will never appear, but for textual data
types one should be prepared to cope with either the presence
or absence of quotes.

By 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 ([]) around each array
dimension's lower and upper bounds, with a colon (:) delimiter character in between. The array
dimension decoration is followed by an equal sign (=). For example:

The array output routine will include explicit dimensions in
its result only when there are one or more lower bounds
different from one.

If the value written for an element is NULL (in any case variant), the element is taken
to be NULL. The presence of any quotes or backslashes disables
this and allows the literal string value "NULL" to be entered. Also, for backwards
compatibility with pre-8.2 versions of PostgreSQL, the array_nulls
configuration parameter can be turned off to suppress recognition of NULL as a NULL.

As shown previously, when writing an array value you can use
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 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 NULL must be quoted, too. To put a double quote
or backslash in a quoted array element value, use escape string
syntax and 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.

You 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.

Note: 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 (E'{"\\\\","\\""}');

The escape string 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.) Dollar quoting (see Section
4.1.2.4) can be used to avoid the need to double
backslashes.

Tip: The ARRAY
constructor syntax (see Section
4.2.11) is often easier to work with than the
array-literal syntax when writing array values in SQL
commands. In ARRAY, individual
element values are written the same way they would be
written when not members of an array.

Submit correction

If you see anything in the documentation that is not correct, does not match
your experience with the particular feature or requires further clarification,
please use
this form
to report a documentation issue.