# ARRAY_TO_STRING() Function in PostgreSQL

> PostgreSQL offers an ARRAY_TO_STRING() function that accepts three arguments: an array, a delimiter, and a text to replace the null values.

PostgreSQL offers a built-in array function named **ARRAY_TO_STRING()** that accepts an array, converts it into strings, and concatenates the strings using a delimiter/separator. The separator can be any value, such as white space, comma, semi-colon, etc.

This write-up will teach you how to use the **ARRAY_TO_STRING()** function in Postgres via suitable examples. So, let’s start!

 **How to Use ARRAY_TO_STRING() Function in Postgres?**

In Postgres, the below-mentioned syntax is used for the ARRAY_TO_STRING() function:
    
    
    ARRAY_TO_STRING(arr, sep[, text]);

In the above syntax:

\- “arr” represents an array to be converted to strings.  
\- “sep” represents a separator/delimiter that will be used between the strings.  
\- “text” is an optional parameter that replaces the NULL values with the specified text.

The return type of the ARRAY_TO_STRING() function will be TEXT.

 **Example 1: How to Use ARRAY_TO_STRING() Function in Postgres?**

Let’s learn the working of the ARRAY_TO_STRING() function using the below code:
    
    
    SELECT ARRAY_TO_STRING(
    ARRAY[1, 23, 150, -1, 2, -3, -1, 12, 121], ',', '0'
    );

In this example:

\- An array is passed as the first argument.  
\- A comma is passed as a separator.  
\- And “0” is passed as the third parameter that will replace the NULL value(if any) with the 0.

The output snippet proves that the given array has been converted into a string, and a comma is concatenated with each array element.

 **Example 2: How Does the ARRAY_TO_STRING() Function Deals With the Null Values in Postgres?**

In the below-given input array, there are some NULL values. We will pass “0” as the third parameter to the ARRAY_TO_STRING() function. Consequently, it will replace the NULL value(if any) with 0:
    
    
    SELECT ARRAY_TO_STRING(
    ARRAY[1, 23, NULL, -1, 2, NULL, -1, NULL, 121], ',', '0'
    );

The output shows that the NULL values have been replaced with the specified null_text, i.e., “0”.

 **Example 3: How to Use ARRAY_TO_STRING() Function on Table’s Data in Postgres?**

We have created a table named “st_information” that contains the following data:
    
    
    SELECT * FROM st_information;

Let’s use the ARRAY_TO_STRING() function on “st_name” array to convert it into a string:
    
    
    SELECT ARRAY_TO_STRING(st_name, ':', '-')
    FROM st_information;

In the above example code:

\- An array-type column is passed as the first argument to the ARRAY_TO_STRING() function.  
\- A colon is passed as a separator/delimiter.  
\- “-” is passed as the third parameter that will replace the NULL value(if any) with “-”.

The output clarifies that the input array has been converted into a string.

That’s it from this Postgres tutorial!

 **Conclusion**

PostgreSQL offers a built-in array function named **ARRAY_TO_STRING()** that accepts three arguments: an array, a delimiter/separator, and a text to replace the null values. The **ARRAY_TO_STRING()** function converts the given array into strings and concatenates the strings using a delimiter/separator. The return type of the ARRAY_TO_STRING() function is TEXT. Postgres ARRAY_TO_STRING() function is explained with practical examples in this write-up.

---
[View this page online](https://www.commandprompt.com/education/array_to_string-function-in-postgresql/)