ARRAY_TO_STRING

Concatenate array elements into a string with a delimiter, optionally replacing NULL elements.

Category: stringReturns: VARCHARDialect: PostgreSql

Syntax

ARRAY_TO_STRING(array, delimiter [, null_string])

Description

## Overview Concatenates the elements of an array into a single string with the specified delimiter between consecutive elements. ARRAY_TO_STRING is the inverse of STRING_TO_ARRAY. It is the go-to function for rendering array columns as display strings and for building CSV-style payloads from aggregated arrays. The two-argument form omits NULL elements silently. Pass a third argument to replace NULLs with an explicit sentinel, which is often safer because it preserves the element count and the positional correspondence between input and output. ## Behavior - Returns NULL when the array itself is NULL. - Returns an empty string when the array is empty. - Without null_string, NULL elements are skipped; the delimiter is not duplicated around them. - With null_string provided, NULL elements are replaced by that sentinel and the delimiter appears on both sides. - Non-string elements are cast to their default text representation before joining. - Operates on Unicode code points, producing valid UTF-8 output. - The delimiter is treated literally; regex metacharacters are not interpreted. ## Compatibility - Matches the common SQL ARRAY_TO_STRING semantics, including the three-argument null_string form.

Parameters

Name	Type	Description
`array`		Specifies the input array whose elements are concatenated. Accepts any array type; elements are cast to text before joining.
`delimiter`		Specifies the separator inserted between consecutive elements.
`null_string`		Specifies a replacement string for NULL array elements. When omitted, NULL elements are skipped entirely.

Examples

-- Basic join
SELECT ARRAY_TO_STRING(ARRAY[1, 2, 3], ',');  -- '1,2,3'

-- Multi-character delimiter
SELECT ARRAY_TO_STRING(ARRAY['a', 'b', 'c'], ' - ');  -- 'a - b - c'

-- NULL elements are skipped by default
SELECT ARRAY_TO_STRING(ARRAY['x', NULL, 'z'], ',');  -- 'x,z'

-- Replace NULL elements with a sentinel
SELECT ARRAY_TO_STRING(ARRAY['x', NULL, 'z'], ',', 'N/A');  -- 'x,N/A,z'

-- Empty array returns empty string
SELECT ARRAY_TO_STRING(ARRAY[]::VARCHAR[], ',');  -- ''

-- Input array is NULL
SELECT ARRAY_TO_STRING(CAST(NULL AS VARCHAR[]), ',');  -- NULL

Pitfalls

Without null_string, NULL elements are silently skipped. This changes the length of the output compared to the input array and can hide data quality issues.
Non-string element types are cast using the default text representation. Floats may pick up precision artefacts; dates pick up the default ISO format. Cast explicitly with FORMAT for stable output.
ARRAY_TO_STRING does not escape embedded delimiter characters. If an element contains the delimiter, the round-trip through STRING_TO_ARRAY will over-split.
The empty-array case returns an empty string, not NULL. Distinguish the two in downstream logic if 'no tags' and 'some tags' must be handled differently.