PostgreSQL offers a variety of built-in functions that are used to perform different functionalities on the arrays. For instance, ARRAY_APPEND() function appends an element to an array, ARRAY_LENGTH() function finds the length of an array, the ARRAY_REMOVE() function removes the elements from an array, and so on. ARRAY_CAT() is another very convenient function in Postgres that is used to concatenate two arrays.

This write-up will teach you how to concatenate two arrays using the ARRAY_CAT() function in PostgreSQL. So, let’s get started!

How to Use ARRAY_CAT() Function in Postgres?

The ARRAY_CAT() function accepts two arrays as arguments and retrieves a concatenated array. For this purpose, use the below-mentioned syntax:

ARRAY_CAT(arr_1, arr_2);

In the above snippet, arr_1 and arr_2 represent the arrays to be concatenated.

Example 1: How to Concatenate Two NUMERIC Arrays in Postgres?

Suppose we want to concatenate the following two arrays: [1, 23, 150, -1, 2, 0, 12, 14] and [-3, -1, 12, 121, 21, 32, 54]. To do that, we will pass both these arrays as arguments to the ARRAY_CAT() function:

SELECT ARRAY_CAT(
ARRAY[1, 23, 150, -1, 2, 0, 12, 14], 
ARRAY[-3, -1, 12, 121, 21, 32, 54]
);

The output shows that the ARRAY_CAT() function successfully concatenated the input arrays. The data type of the retrieved array is INT.

Example 2: How to Concatenate Two STRING Arrays in Postgres?

Let’s learn how to concatenate two string-type arrays using the ARRAY_CAT() function:

SELECT ARRAY_CAT(
ARRAY['Ambrose', 'John', 'Joe', 'Joseph'], 
ARRAY['Natie', 'Alexa', 'Anna', 'Stephanie']
);

The output proves that the ARRAY_CAT() function successfully concatenated the given string arrays. The data type of the retrieved array is TEXT.

Example 3: How to ARRAY_CAT() Function on Table’s Data?

We have created a table named st_information that contains the following data:

SELECT * FROM st_information;

The above snippet shows that the st_information table has two string-type arrays. To concatenate both these arrays, you can use the ARRAY_CAT() function as follows:

SELECT ARRAY_CAT(st_name, st_email)
FROM st_information;

The output snippet proves that both arrays have been merged successfully. The data type of the retrieved array is TEXT.

That’s it from this Postgres guide!

Conclusion

ARRAY_CAT() is another very convenient function in Postgres that is used to concatenate two arrays. For this purpose, the ARRAY_CAT() function accepts two arrays as arguments and retrieves a concatenated array. The data type of the retrieved array depends on the input arrays. Postgres ARRAY_CAT() function is explained with practical examples in this write-up.