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}}