String to array and array to string in PostgreSQL - Step-by-Step Execution

Concept Flow - String to array and array to string

Start with string

↓

Use string_to_array()

↓

Get array output

↓

Use array_to_string()

↓

Get string output

↓

End

Convert a string into an array using string_to_array(), then convert an array back to a string using array_to_string().

Execution Sample

PostgreSQL

SELECT string_to_array('apple,banana,cherry', ',') AS arr;
SELECT array_to_string(ARRAY['apple','banana','cherry'], ',') AS str;

Convert a comma-separated string to an array, then convert an array back to a comma-separated string.

Execution Table

Step	Function Called	Input	Output	Explanation
1	string_to_array	'apple,banana,cherry', ','	{apple,banana,cherry}	Splits string by comma into array elements
2	array_to_string	ARRAY['apple','banana','cherry'], ','	'apple,banana,cherry'	Joins array elements into string separated by comma
3	End	-	-	Conversion complete

💡 All conversions done, no more steps

Variable Tracker

Variable	Start	After Step 1	After Step 2	Final
input_string	'apple,banana,cherry'	'apple,banana,cherry'	'apple,banana,cherry'	'apple,banana,cherry'
array_value	NULL	{apple,banana,cherry}	{apple,banana,cherry}	{apple,banana,cherry}
output_string	NULL	NULL	'apple,banana,cherry'	'apple,banana,cherry'

Key Moments - 2 Insights

Why does string_to_array need a delimiter?

string_to_array splits the string at each delimiter to create array elements, so without a delimiter it cannot separate parts (see execution_table step 1).

What happens if array_to_string uses a different delimiter than string_to_array?

The output string will join array elements with the new delimiter, changing the string format (see execution_table step 2).

Visual Quiz - 3 Questions

Look at the execution_table, what is the output of string_to_array at step 1?

A ARRAY['apple','banana','cherry']

B {apple,banana,cherry}

C 'apple,banana,cherry'

D NULL

💡 Hint

Check the Output column in execution_table row with Step 1

At which step does the output become a string again?

A Step 2

B Step 1

C Step 3

D Never

💡 Hint

Look at the Output column and Function Called in execution_table

If the delimiter in string_to_array was changed to ';', what would happen?

A The array would split at commas

B The array would be empty

C The array would have one element with the whole string

D The function would error

💡 Hint

string_to_array splits only at the delimiter given (see key_moments about delimiter)

Concept Snapshot

string_to_array(text, delimiter) splits a string into an array by delimiter
array_to_string(array, delimiter) joins array elements into a string
Use matching delimiters for reversible conversion
Useful for parsing CSV-like strings
Both functions are built-in PostgreSQL

Full Transcript

This visual execution shows how PostgreSQL converts a string to an array and back. First, string_to_array splits the input string 'apple,banana,cherry' by commas into an array with three elements. Then, array_to_string joins the array elements back into a single string separated by commas. The variable tracker shows the string and array values at each step. Key moments explain why the delimiter is important for splitting and joining. The quiz tests understanding of outputs at each step and the effect of changing delimiters.

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