In PostgreSQL we can use the array_to_string() function to convert an array to a string. It converts each array element to its text representation (if needed), and then concatenates all elements using the given delimiter.

Any null values are omitted from the result, but we do have the option of replacing any null values with a given text value.

Example

Here’s an example to demonstrate:

SELECT array_to_string(ARRAY[1, 2, 3], '-');

Result:

1-2-3

In this case I used a hyphen as the separator, but we can just as easily use a comma (or any other character):

SELECT array_to_string(ARRAY[1, 2, 3], ',');

Result:

1,2,3

Dealing with Null Values

As mentioned, null values are omitted from the result:

SELECT array_to_string(ARRAY[1, null, 3, null, 5], '-');

Result:

1-3-5

But we also have the option to provide a separate text value for which to replace any null values with:

SELECT array_to_string(ARRAY[1, null, 3, null, 5], '-', 'null');

Result:

1-null-3-null-5

Here I replaced null values with the actual text null, but this could have been anything:

SELECT array_to_string(ARRAY[1, null, 3, null, 5], '-', '0');

Result:

 1-0-3-0-5

Bear in mind that if we do want to replace null values with the actual text null, then we need to surround that text with single quotes. If we just provide null without the single quotes, then it has no effect (i.e. null values are removed from the resulting string).

Here’s an example of what I mean:

SELECT array_to_string(ARRAY[1, null, 3, null, 5], '-', null);

Result:

1-3-5

So we can see that the last argument is actually null (instead of the string literal 'null'), and so null values are not replaced with any text; they’re simply removed as they would normally be if we hadn’t provided a third argument.

Multi Dimensional Arrays

We can use array_to_string() on multi dimensional arrays:

SELECT array_to_string(ARRAY[ARRAY[1, 2], ARRAY[3,4]], '-');

Result:

1-2-3-4