In PostgreSQL, we can use the || operator to concatenate two arrays. We can also use it to append or prepend a (compatible) non-array value to an array.

Concatenate Two Arrays

Here’s a quick example to demonstrate how we can use the operator to concatenate two arrays:

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

Result:

{1,2,3,4,5,6}

In that example I built each array using the ARRAY constructor. We can also provide array literals:

SELECT '{1,2,3}'::integer[] || '{4,5,6}'::integer[];

Result:

{1,2,3,4,5,6}

But we need to be careful when doing this. If we don’t explicitly cast at least one of the arrays as an array, then we could end up with two separate arrays (or more accurately, one string that looks like two separate arrays).

Like this:

SELECT '{1,2,3}' || '{4,5,6}';

Result:

{1,2,3}{4,5,6}

In this example PostgreSQL thought we were concatenating two strings, and so that’s what it did.

Concatenate an Array with a Non-Array

We can also use the || operator to concatenate an array with a compatible non-array value. When we do this, the non-array value is either prepended or appended to the array (depending on which side the array is on).

Example:

SELECT ARRAY[1,2,3] || 4;

Result:

{1,2,3,4}

In this case, an integer was concatenated to the end of the array.

Here are some more examples:

SELECT 
    ARRAY[ 'Cat', 'Dog' ] || '{Horse}' AS "1",
    '{Horse}' || ARRAY[ 'Cat', 'Dog' ] AS "2",
    '{1,2,3}'::integer[] || '{4,5,6}' AS "3";

Result:

        1        |        2        |       3       
-----------------+-----------------+---------------
 {Cat,Dog,Horse} | {Horse,Cat,Dog} | {1,2,3,4,5,6}

But sometimes we need to try to make the non-array at least look like an array. Here’s an example of what happens if I remove the brackets from one of the above strings:

SELECT ARRAY[ 'Cat', 'Dog' ] || 'Horse';

Result:

ERROR:  malformed array literal: "Horse"
LINE 1: SELECT ARRAY[ 'Cat', 'Dog' ] || 'Horse';
                                        ^
DETAIL:  Array value must start with "{" or dimension information.

When concatenating an array with a non-array value, the array argument must be either empty or one dimensional. Here’s what happens if we try to concatenate a value with a multi dimensional array:

SELECT ARRAY[ ARRAY[1,2,3] ] || 4;

Result:

ERROR:  argument must be empty or one-dimensional array

However, we can get a successful result if the array is empty:

SELECT ARRAY[ ARRAY[ ] ]::integer[] || 4;

Result:

{4}

That said, a one dimensional array was returned despite me trying to give it two dimensions.

Concatenating NULL Values

Concatenating an array with a NULL value results in the array being returned:

SELECT ARRAY[ 'Cat', 'Dog' ] || NULL;

Result:

{Cat,Dog}

But concatenating two NULLs result in NULL:

SELECT NULL || NULL;

Result:

NULL

Concatenating Empty Arrays

Concatenating a non-empty array with an empty one returns the non-empty array:

SELECT ARRAY[ 'Cat', 'Dog' ] || '{}';

Result:

{Cat,Dog}

But concatenating two empty arrays results in an empty array

SELECT '{}'::text[] || '{}';

Result:

{}

But remember that this requires at least one of the arrays to be explicitly defined as an array. Here’s what happens if I remove the explicit cast from the first array:

SELECT '{}' || '{}';

Result:

{}{}

Concatenating Arrays with Different Dimensions

When concatenating two arrays, there can’t be any more than one dimension in difference.

So this is possible:

SELECT ARRAY[1,2,3] || ARRAY[[4,5,6],[7,8,9]];

Result:

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

But here’s what happens if we nest the second array inside another array:

SELECT ARRAY[1,2,3] || ARRAY[ARRAY[[4,5,6],[7,8,9]]];

Result:

ERROR:  cannot concatenate incompatible arrays
DETAIL:  Arrays of 1 and 3 dimensions are not compatible for concatenation.

We could continue this by adding yet another array:

SELECT ARRAY[1,2,3] || ARRAY[ARRAY[ARRAY[[4,5,6],[7,8,9]]]];

Result:

ERROR:  cannot concatenate incompatible arrays
DETAIL:  Arrays of 1 and 4 dimensions are not compatible for concatenation.