Array to String in PostgreSQL – array_to_string()

The array_to_string() function in PostgreSQL is used to convert an array into a single string, with elements of the array separated by a specified delimiter. This function is particularly useful when you need to concatenate elements of an array into a single string.

Syntax – array_to_string() function in PostgreSQL

• array: The array to be converted into a string
• delimiter: The string used to separate the elements of the array in the resulting string.

Convert Array to String with Comma Separator

 

SELECT array_to_string(ARRAY['Mercury', 'Venus', 'Earth','Mars'], ',');

Explanation: Splits the string ‘Mercury,Venus,Earth,Mars’ into an array with ‘Mercury’, ‘Venus’, ‘Earth’ and ‘Mars’ as elements. Converts the array {‘Mercury’,’Venus’,’Earth’,’Mars’} into a string with elements separated by commas.

Output:

Convert Array to String with Space Separator

 

SELECT array_to_string(ARRAY['Hello','PostgreSQL', 'Enthusiast'], ' ');

Explanation: Converts the array {‘Hello’,’PostgreSQL’, ‘Enthusiast’} into a string with elements separated by spaces.

Output:

Convert array to string with Dash / hyphen  

 

SELECT array_to_string(ARRAY['20','AUG', '2024'], '-');

Explanation: Converts the array {’20’,’AUG’, ‘2024’} into a string with elements separated by Dash or hyphen

Output:

To Convert Array to String on a Column in PostgreSQL:

You can use array_to_string() function to convert the array values of a column in a table into string.

Let’s use the table name “orders_arr_table”

 

SELECT *, array_to_string(order_items, ', ') AS items_string
FROM orders_arr_table;

array_to_string() Converts Entire order_items  column into string column as shown in the above code, so the output will be

Output: