How to Use array_cat Function in PostgreSQL

In PostgreSQL, use the array_cat function to join two arrays into a single array by concatenating their elements. The syntax is array_cat(array1, array2), where both inputs are arrays of the same data type.

Syntax

The array_cat function takes two arrays as input and returns a new array that contains all elements of the first array followed by all elements of the second array.

• array_cat(array1, array2): Concatenates array1 and array2.
• Both array1 and array2 must be arrays of the same data type.
• The result is a single array containing elements of both arrays in order.

sql

array_cat(anyarray, anyarray) returns anyarray

Example

This example shows how to concatenate two integer arrays using array_cat. It returns a combined array with elements from both arrays.

sql

SELECT array_cat(ARRAY[1, 2, 3], ARRAY[4, 5, 6]) AS combined_array;

Output

 combined_array 
----------------
 {1,2,3,4,5,6}
(1 row)

Common Pitfalls

Common mistakes when using array_cat include:

• Passing arrays of different data types, which causes an error.
• Using NULL arrays without handling, which results in NULL output.
• Confusing array_cat with array append operators that add single elements.

sql

/* Wrong: different data types */
-- SELECT array_cat(ARRAY[1, 2], ARRAY['a', 'b']); -- ERROR

/* Correct: same data types */
SELECT array_cat(ARRAY[1, 2], ARRAY[3, 4]) AS result;

Output

 result 
--------
 {1,2,3,4}
(1 row)

Quick Reference

Function	Description	Example
array_cat(array1, array2)	Concatenates two arrays into one	array_cat(ARRAY[1,2], ARRAY[3,4]) → {1,2,3,4}
array_append(array, element)	Adds a single element to an array	array_append(ARRAY[1,2], 3) → {1,2,3}
array_prepend(element, array)	Adds a single element at the start	array_prepend(0, ARRAY[1,2]) → {0,1,2}

Key Takeaways

Use array_cat to join two arrays of the same type into one combined array.

Both input arrays must have the same data type to avoid errors.

array_cat returns NULL if either input array is NULL unless handled explicitly.

Do not confuse array_cat with functions that add single elements like array_append.

Use array_cat for merging arrays, useful in queries involving array data.