As a database developer or data analyst, you may face a situation where you have to combine/merge multiple arrays into a single array. This can be challenging, especially when handling large data sets or working with arrays with different data types. So, how to tackle such scenarios?

Thankfully, PostgreSQL provides a built-in function named ARRAY_CAT() and a concatenation operator “||” that assists us in concatenating/combining multiple arrays into a single array.

This blog post will show you how to concatenate multiple arrays into one array using the concatenation operator and the ARRAY_CAT() function. So, let’s get started!

How to Concatenate Multiple Arrays in Postgres Via ARRAY_CAT()?

The ARRAY_CAT() function accepts different arrays as arguments and retrieves a concatenated array. Whether you are working with alphabetic, numeric, or a combination of both, the ARRAY_CAT() function can assist you in each case. Use the following syntax to avail the functionality of the ARRAY_CAT() function:

ARRAY_CAT(arr_1, arr_2, arr_3, … arr_N);

The ARRAY_CAT() function accepts an “n” number of arrays and combines them into one array.

Example: How to Concat Multiple Arrays Via ARRAY_CAT() in Postgres?

We have already created a sample table named “staff_information”, whose data is shown in the following snippet:

Let’s concatenate the “st_name” and “st_email” arrays via the ARRAY_CAT() function:

SELECT ARRAY_CAT(st_name, st_email) 
FROM staff_information;

The output snippet shows that the targeted arrays have been combined successfully.

How to Concatenate Multiple Arrays in Postgres Via Concatenation Operator?

You can also use the concatenation operator between different arrays to combine them into a single array. The below snippet shows the usage of the concatenation operator:

SELECT array_3 || array_2 || … || array_n;

The above syntax shows that the “n” number of arrays can be combined using the concatenation operator.

Example: How to Concat Multiple Arrays Via Concatenation Operator in Postgres?

In the following example, we will show you how to merge multiple arrays using the concatenation operator in Postgres:

SELECT st_name || st_email AS merged_array
FROM staff_information;

The above piece of code will combine the “st_name” and “st_email” arrays of the “staff_inforamation” table:

The output snippet shows that both columns have been concatenated successfully.

This way, you can concatenate two or more arrays in Postgres.

Conclusion

PostgreSQL provides a built-in function named ARRAY_CAT() and a concatenation operator “||” that assists us in concatenating/combining multiple arrays into a single array efficiently. The ARRAY_CAT() function and the concatenation operator accept the “n” number of arrays and combine them into a single array. Through practical examples, this blog post explained how to concatenate multiple arrays into one array using the concatenation operator and the ARRAY_CAT() function.