String to array and array to string in PostgreSQL

Introduction

Sometimes, you have a list of words or values in one string and want to work with each item separately. Other times, you have a list and want to join it back into one string.

You receive a list of tags separated by commas and want to search each tag individually.

You want to store multiple email addresses in one column and later split them to send emails.

You have an array of names and want to display them as a single comma-separated string.

You need to count how many items are in a list stored as a string.

You want to convert user input from a text box into an array for easier processing.

Syntax

PostgreSQL

SELECT string_to_array(text, delimiter) AS array_result;

SELECT array_to_string(array, delimiter) AS string_result;

The delimiter is the character or string that separates items in the text.

string_to_array converts a text string into an array using the delimiter.

array_to_string joins array elements into one string using the delimiter.

Examples

Splits a comma-separated string into an array of fruits.

PostgreSQL

SELECT string_to_array('apple,banana,cherry', ',');

Joins an array of colors into a single string separated by dashes.

PostgreSQL

SELECT array_to_string(ARRAY['red', 'green', 'blue'], '-');

Edge case: empty string returns NULL.

PostgreSQL

SELECT string_to_array('', ',');

Edge case: empty array returns NULL.

PostgreSQL

SELECT array_to_string(ARRAY[]::text[], ',');

Sample Program

This query shows converting a string of animals into an array, and an array of animals back into a string.

PostgreSQL

WITH example AS (
  SELECT 'dog,cat,bird' AS animals_string,
         ARRAY['lion', 'tiger', 'bear'] AS animals_array
)
SELECT
  string_to_array(animals_string, ',') AS animals_array_from_string,
  array_to_string(animals_array, ',') AS animals_string_from_array
FROM example;

Important Notes

Time complexity is generally O(n) where n is the length of the string or array because each element is processed once.

Space complexity depends on the size of the input string or array since a new array or string is created.

A common mistake is forgetting to use the correct delimiter matching the string format.

Use string_to_array when you need to work with individual elements separately, and array_to_string when you want to display or store the array as a single string.

Summary

string_to_array splits a text string into an array using a delimiter.

array_to_string joins array elements into a single string with a delimiter.

These functions help switch between string lists and arrays for easier data handling.