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.

Practice

1. What does the PostgreSQL function string_to_array('apple,banana,cherry', ',') return?

easy

A. An array with one element containing the whole string

B. A single string 'apple,banana,cherry'

C. An array with elements ['apple', 'banana', 'cherry']

D. An error because the delimiter is missing

Solution

1. Step 1: Understand string_to_array function

   This function splits a string into an array using the given delimiter.

2. Step 2: Apply the delimiter ',' to the string

   The string 'apple,banana,cherry' is split at each comma, creating ['apple', 'banana', 'cherry'].

3. Final Answer:

   An array with elements ['apple', 'banana', 'cherry'] -> Option C

4. Quick Check:

   string_to_array splits string by delimiter [OK]

Hint: string_to_array splits text by delimiter into array [OK]

Common Mistakes:

• Thinking it returns a string instead of an array
• Using wrong delimiter causing no split
• Confusing array_to_string with string_to_array

2. Which of the following is the correct syntax to convert an array ARRAY['red','green','blue'] into a string separated by semicolons in PostgreSQL?

easy

A. array_to_string(ARRAY['red','green','blue'], ';')

B. string_to_array(ARRAY['red','green','blue'], ';')

C. array_join(ARRAY['red','green','blue'], ';')

D. join_array(ARRAY['red','green','blue'], ';')

Solution

1. Step 1: Identify function to join array elements

   PostgreSQL uses array_to_string to join array elements into a string with a delimiter.

2. Step 2: Check syntax correctness

   array_to_string(array, delimiter) is correct; other options are invalid functions.

3. Final Answer:

   array_to_string(ARRAY['red','green','blue'], ';') -> Option A

4. Quick Check:

   array_to_string joins array with delimiter [OK]

Hint: Use array_to_string(array, delimiter) to join array elements [OK]

Common Mistakes:

• Using string_to_array instead of array_to_string
• Using non-existent functions like array_join
• Incorrect order of arguments

3. What is the output of this query?

SELECT array_to_string(string_to_array('dog|cat|bird', '|'), ', ');

medium

A. NULL

B. 'dog|cat|bird'

C. 'dogcatbird'

D. 'dog, cat, bird'

Solution

1. Step 1: Apply string_to_array with '|' delimiter

   The string 'dog|cat|bird' is split into array ['dog', 'cat', 'bird'].

2. Step 2: Use array_to_string with ', ' delimiter

   The array elements are joined with ', ' producing 'dog, cat, bird'.

3. Final Answer:

   'dog, cat, bird' -> Option D

4. Quick Check:

   string_to_array then array_to_string changes delimiter [OK]

Hint: Chain string_to_array and array_to_string to change delimiters [OK]

Common Mistakes:

• Expecting original string unchanged
• Missing spaces after commas in output
• Confusing delimiters in functions

4. Identify the error in this query:

SELECT array_to_string(string_to_array('one,two,three'), ',');

medium

A. array_to_string requires two arguments

B. Missing delimiter argument in string_to_array

C. string_to_array cannot be nested inside array_to_string

D. No error, query is correct

Solution

1. Step 1: Check string_to_array usage

   string_to_array requires two arguments: the string and the delimiter.

2. Step 2: Identify missing delimiter

   The query misses the delimiter argument in string_to_array, causing a syntax error.

3. Final Answer:

   Missing delimiter argument in string_to_array -> Option B

4. Quick Check:

   string_to_array needs delimiter [OK]

Hint: Always provide delimiter to string_to_array [OK]

Common Mistakes:

• Omitting delimiter in string_to_array
• Thinking array_to_string needs more arguments
• Assuming nesting functions causes error

5. You have a column tags storing comma-separated strings like 'red,blue,green'. You want to find rows where the tag 'blue' exists. Which query correctly uses string_to_array and array_to_string to help filter these rows?

hard

A. SELECT * FROM table WHERE 'blue' = ANY(string_to_array(tags, ','));

B. SELECT * FROM table WHERE array_to_string(tags, ',') LIKE '%blue%';

C. SELECT * FROM table WHERE 'blue' = string_to_array(tags, ',');

D. SELECT * FROM table WHERE 'blue' IN array_to_string(tags, ',');

Solution

1. Step 1: Convert tags string to array

   Use string_to_array(tags, ',') to split tags into an array.

2. Step 2: Check if 'blue' is in the array

   Use 'blue' = ANY(array) to test if 'blue' exists in the array.

3. Final Answer:

   SELECT * FROM table WHERE 'blue' = ANY(string_to_array(tags, ',')); -> Option A

4. Quick Check:

   Use string_to_array + ANY to filter arrays [OK]

Hint: Use 'value' = ANY(string_to_array(column, delimiter)) to filter [OK]

Common Mistakes:

• Using array_to_string to filter arrays
• Comparing string to array directly
• Using IN with array_to_string incorrectly