In PostgreSQL, we can use the array_cat() function to concatenate two arrays.

We pass both arrays as arguments when we call the function.

Example

Here’s an example to demonstrate:

SELECT array_cat(array['Cat', 'Dog'], array['Bird', 'Zebra']);

Result:

{Cat,Dog,Bird,Zebra}

We can see that the second array was concatenated to the first.

This is the equivalent of the following:

SELECT array['Cat', 'Dog'] || array['Bird', 'Zebra'];

Result:

{Cat,Dog,Bird,Zebra}

In this case I used the concatenation operator (||) to concatenate the arrays.

Append/Prepend an Element

Any of the arrays can contain a single element. In this case it’s kind of like appending or prepending a single element to the other array, except that we’re still concatenating two arrays.

Example:

SELECT 
    array_cat(array['Cat', 'Dog'], array['Bird']) AS "Append an Element",
    array_cat(array['Bird'], array['Cat', 'Dog']) AS "Prepend an Element";

Result:

 Append an Element | Prepend an Element 
-------------------+--------------------
 {Cat,Dog,Bird}    | {Bird,Cat,Dog}

So that’s like doing the following:

SELECT 
    array_append(array['Cat', 'Dog'], 'Bird') AS "Append an Element",
    array_prepend('Bird', array['Cat', 'Dog']) AS "Prepend an Element";

Result:

 Append an Element | Prepend an Element 
-------------------+--------------------
 {Cat,Dog,Bird}    | {Bird,Cat,Dog}

Here I used the array_append() and array_prepend() functions to do the job. In this case, the element to append/prepend is provided as a string literal; not as an array.

Multi Dimensional Arrays

We can include multi dimensional arrays in the operation:

SELECT array_cat(
    array['Cat', 'Dog'], 
    array[
        array['Bird', 'Zebra']
        ]
    );

Result:

{{Cat,Dog},{Bird,Zebra}}

In this case the first array was a one dimensional array and the second was two dimensional. That produced the same result as the following:

SELECT array_cat(
    array[
        array['Cat', 'Dog']
        ], 
    array[
        array['Bird', 'Zebra']
        ]
    );

Result:

{{Cat,Dog},{Bird,Zebra}}