Array types

Use array literals

Enclose values in curly braces and separate them with commas. Wrap the whole literal in single quotes:

INSERT INTO sal_emp VALUES
    ('Bill',
     '{10000, 10000, 10000, 10000}',
     '{{"meeting", "lunch"}, {"training", "presentation"}}');

INSERT INTO sal_emp VALUES
    ('Carol',
     '{20000, 25000, 25000, 25000}',
     '{{"breakfast", "consulting"}, {"meeting", "lunch"}}');

The general format is:

'{ val1 delim val2 delim ... }'

where delim is the delimiter character for the type, as recorded in its pg_type entry. Among the standard data types, all use a comma (,), except for type box, which uses a semicolon (;).

For multidimensional arrays, nest curly braces:

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

All sub-arrays in a dimension must have the same number of elements. A mismatch raises an error:

INSERT INTO sal_emp VALUES
    ('Bill',
     '{10000, 10000, 10000, 10000}',
     '{{"meeting", "lunch"}, {"meeting"}}');
-- ERROR: multidimensional arrays must have array expressions with matching dimensions

To set an element to NULL, write NULL (case-insensitive). To store the literal string NULL, double-quote it: "NULL".

Input quoting rules: You must double-quote an element value if it contains curly braces, commas (or the type's delimiter character), double quotes, backslashes, or leading or trailing whitespace. Empty strings and strings matching the word NULL must also be quoted. As an alternative to double-quoting, you can use backslash-escaping to protect any character that would otherwise be interpreted as array syntax.

Whitespace handling: You can add whitespace before a left brace or after a right brace, and before or after any individual item string — this whitespace is ignored. However, whitespace within double-quoted elements, or surrounded on both sides by non-whitespace characters of an element, is preserved.

Use the ARRAY constructor

The ARRAY[...] constructor is often cleaner in SQL statements because element values use standard SQL quoting (single quotes for strings):

INSERT INTO sal_emp VALUES
    ('Bill',
     ARRAY[10000, 10000, 10000, 10000],
     ARRAY[['meeting', 'lunch'], ['training', 'presentation']]);

INSERT INTO sal_emp VALUES
    ('Carol',
     ARRAY[20000, 25000, 25000, 25000],
     ARRAY[['breakfast', 'consulting'], ['meeting', 'lunch']]);

Access a single element

-- Retrieve names of employees whose pay changed in Q2
SELECT name FROM sal_emp WHERE pay_by_quarter[1] <> pay_by_quarter[2];

-- Retrieve Q3 pay for all employees
SELECT pay_by_quarter[3] FROM sal_emp;

Accessing a subscript outside the array bounds returns NULL, not an error. Similarly, an array reference with the wrong number of subscripts yields NULL rather than an error.

Access a slice

Use lower-bound:upper-bound notation to retrieve a subarray:

-- First item on Bill's schedule for the first two days
SELECT schedule[1:2][1:1] FROM sal_emp WHERE name = 'Bill';
--  schedule
-- ------------------------
--  {{meeting},{training}}

-- Omit bounds to use the array's actual limits
SELECT schedule[:2][2:] FROM sal_emp WHERE name = 'Bill';
--  schedule
-- ------------------------
--  {{lunch},{presentation}}

When any dimension is written as a slice, all dimensions are treated as slices. A bare number like [2] is treated as [1:2]. Use explicit slice syntax for all dimensions (for example, [1:2][1:1] rather than [2][1:1]) to avoid ambiguity.

Selecting a slice entirely outside the array bounds returns an empty (zero-dimensional) array, not NULL. (This does not match non-slice behavior and is done for historical reasons.) A partially overlapping slice is silently reduced to the overlapping region.

Inspect dimensions

SELECT array_dims(schedule) FROM sal_emp WHERE name = 'Carol';
--  [1:2][1:2]

SELECT array_upper(schedule, 1) FROM sal_emp WHERE name = 'Carol';
--  2

SELECT cardinality(schedule) FROM sal_emp WHERE name = 'Carol';
--  4

cardinality returns the same count as the number of rows that unnest would produce.

Replace the entire array

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

-- Using ARRAY constructor syntax
UPDATE sal_emp SET pay_by_quarter = ARRAY[25000,25000,27000,27000]
    WHERE name = 'Carol';

Update a single element or slice

-- Update element 4
UPDATE sal_emp SET pay_by_quarter[4] = 15000
    WHERE name = 'Bill';

-- Update a slice
UPDATE sal_emp SET pay_by_quarter[1:2] = '{27000,27000}'
    WHERE name = 'Carol';

Assigning to an element beyond the current array length extends the array automatically. Positions between the old end and the new element are filled with NULLs. This works for one-dimensional arrays only.

Subscripted assignment can also create arrays with non-1-based subscripts. For example, assigning to myarray[-2:7] creates an array with subscript values from -2 to 7.

Concatenate arrays

Use the || operator to join arrays:

SELECT ARRAY[1,2] || ARRAY[3,4];
--  {1,2,3,4}

SELECT ARRAY[5,6] || ARRAY[[1,2],[3,4]];
--  {{5,6},{1,2},{3,4}}

|| handles element-onto-array, array-onto-element, and array-onto-array cases. When a single element is pushed onto either end of a one-dimensional array, the result retains the lower bound subscript of the array operand. 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. For example:

SELECT array_dims(1 || '[0:1]={2,3}'::int[]);
--  [0:2]

SELECT array_dims(ARRAY[1,2] || ARRAY[3,4,5]);
--  [1:5]

When the operand type is ambiguous, use the explicit functions instead:

The explicit functions are useful when the parser cannot determine types unambiguously:

SELECT ARRAY[1, 2] || NULL;                 -- NULL is treated as an array; result: {1,2}
SELECT array_append(ARRAY[1, 2], NULL);     -- appends a NULL element; result: {1,2,NULL}

ANY and ALL

ANY and ALL are the most common ways to search array columns without knowing the array size:

-- Rows where at least one element equals 10000
SELECT * FROM sal_emp WHERE 10000 = ANY (pay_by_quarter);

-- Rows where all elements equal 10000
SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter);

Overlap search with &&

The && operator returns rows where the array column overlaps with the given array — useful for multi-tag filtering:

SELECT * FROM sal_emp WHERE pay_by_quarter && ARRAY[10000];

Find element positions

-- First occurrence: returns subscript 2
SELECT array_position(ARRAY['sun','mon','tue','wed','thu','fri','sat'], 'mon');

-- All occurrences: returns {1,4,8}
SELECT array_positions(ARRAY[1, 4, 3, 1, 3, 4, 2, 1], 1);

Iterate with generate_subscripts

For pattern matching (such as LIKE) or other operations that ANY does not support, iterate over subscripts:

SELECT * FROM
   (SELECT pay_by_quarter,
           generate_subscripts(pay_by_quarter, 1) AS s
      FROM sal_emp) AS foo
 WHERE pay_by_quarter[s] = 10000;

Output format

Array values are serialized as:

• 1D: {val1,val2,val3}

• 2D: {{val1,val2},{val3,val4}}

Element values are double-quoted in output if they are empty strings, contain curly braces, commas, double quotes, backslashes, or whitespace, or match the word NULL. For numeric types, double quotes never appear. For text types, be prepared for either format.

Custom lower bounds

By default, all dimension lower bounds are 1. To represent arrays with different lower bounds, specify the subscript ranges explicitly before the array contents:

SELECT f1[1][-2][3] AS e1, f1[1][-1][5] AS e2
 FROM (SELECT '[1:1][-2:-1][3:5]={{{1,2,3},{4,5,6}}}'::int[] AS f1) AS ss;

--  e1 | e2
-- ----+----
--   1 |  6

Explicit dimension decorations appear in output only when one or more lower bounds differ from 1.

NULL handling in input

Write NULL (any case) to set an element to NULL. To store the literal string NULL, double-quote it: "NULL".

For backward 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.