String to array and array to string in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Convert a comma-separated string to an array

What is the result of this PostgreSQL query that converts a string to an array?

PostgreSQL

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

A {apple,banana,cherry}

B ['apple','banana','cherry']

C apple,banana,cherry

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

💡 Hint

Use the string_to_array function with the delimiter as a comma.

The string_to_array function splits the string by the delimiter and returns a PostgreSQL array shown with curly braces.

❓ query_result

intermediate

Convert an array to a string with a delimiter

What does this PostgreSQL query return when converting an array to a string separated by semicolons?

PostgreSQL

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

A red;green;blue

B {red;green;blue}

C ARRAY['red;green;blue']

D red,green,blue

💡 Hint

array_to_string joins array elements using the given delimiter.

array_to_string returns a single string with array elements joined by the delimiter.

📝 Syntax

advanced

Identify the syntax error in string to array conversion

Which option contains a syntax error when trying to convert a string to an array in PostgreSQL?

PostgreSQL

SELECT string_to_array('dog|cat|mouse', '|');

A SELECT string_to_array('dog|cat|mouse', '|');

B ;)'|' ,'esuom|tac|god'(yarra_ot_gnirts TCELES

C SELECT string_to_array('dog|cat|mouse' '|');

D ELECT string_to_array('dog|cat|mouse', '|');

💡 Hint

Check for missing commas between function arguments.

Option C is missing a comma between the string and delimiter arguments, causing a syntax error.

❓ query_result

advanced

Using array_to_string with NULL elements

What is the output of this query that converts an array with NULL elements to a string?

PostgreSQL

SELECT array_to_string(ARRAY['a', NULL, 'c'], ',');

A a,c

B a,,c

C a,NULL,c

D a,,NULL,c

💡 Hint

NULL elements become empty strings in the output.

array_to_string replaces NULL elements with empty strings, so two commas appear with nothing between.

🧠 Conceptual

expert

Choosing the correct function for string to array conversion with multiple delimiters

You have a string 'one;two,three|four' and want to split it into an array using any of the delimiters ';', ',', or '|'. Which PostgreSQL approach correctly achieves this?

A Use array_to_string('one;two,three|four', '[;,|]')

B Use string_to_array('one;two,three|four', ';,|')

C Use split_part('one;two,three|four', '[;,|]', 1)

D Use regexp_split_to_array('one;two,three|four', '[;,|]')

💡 Hint

string_to_array only accepts a single delimiter character, not multiple.

regexp_split_to_array allows splitting by a regular expression matching multiple delimiters, unlike string_to_array.

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