Joins the elements of an array into one string with a separator.
ARRAY_TO_STRING(<array>, <separator>)
ARRAY_TO_STRING(<array>, <separator>, <null replacement>)
The opposite of STRING_TO_ARRAY: it turns an array into text. By default it skips NULL elements, and the third argument sets what to replace them with. Similar to STRING_AGG, but it works with a ready-made array in a single row rather than collecting values from many rows.
employees
| name | department | salary | bonus | phone |
|---|---|---|---|---|
| Emma | backend | 1200 | 300 | +44 7700 900123 |
| Liam | backend | 1000 | NULL | NULL |
| Olivia | frontend | 1350 | 150 | +44 7700 900456 |
| Noah | frontend | 950 | NULL | +44 7700 900789 |
| Sophia | design | 1200 | 200 | NULL |
These tables already exist in the sandbox — the examples below use them. Want to try this in your own database? Copy the SQL.
| tags |
|---|
| sql, postgres, database |
| skipped | replaced |
|---|---|
| Emma, Liam | Emma, —, Liam |
| department | phones |
|---|---|
| backend | +44 7700 900123; no phone |
| design | no phone |
| frontend | +44 7700 900789; +44 7700 900456 |
STRING_AGG would silently skip people without a phone, but here you can see that someone has none.
NULL elements silently disappear, along with any trace that a value was missing. If you need to see the gaps, pass the third argument.
The separator is required: ARRAY_TO_STRING(ARRAY['a']) throws function array_to_string(text[]) does not exist. For no separator — ARRAY_TO_STRING(<array>, '').