String to array and array to string in PostgreSQL - Mini Project: Build & Apply

String to Array and Array to String in PostgreSQL

📖 Scenario: You are managing a contact list database. Each contact has a list of phone numbers stored as a single string separated by commas. You want to convert this string into an array to work with individual phone numbers easily. Later, you want to convert the array back into a string to store or display.

🎯 Goal: Learn how to convert a comma-separated string into an array using PostgreSQL functions, and then convert an array back into a string.

📋 What You'll Learn

Create a table called contacts with columns id (integer) and phone_numbers (text).

Insert a row with id = 1 and phone_numbers as a comma-separated string of phone numbers.

Write a query to convert the phone_numbers string into a text array.

Write a query to convert the text array back into a comma-separated string.

💡 Why This Matters

🌍 Real World

Many databases store lists as strings. Converting between strings and arrays helps manipulate individual items easily.

💼 Career

Database developers and analysts often need to parse and format list data stored as strings for reporting and processing.

1

Create the contacts table and insert data

Create a table called contacts with columns id as integer and phone_numbers as text. Insert one row with id = 1 and phone_numbers set to the string '123-456-7890,987-654-3210,555-555-5555'.

PostgreSQL

-- Create the contacts table and insert one row
-- Your code here

Hint

Use CREATE TABLE to define the table and INSERT INTO to add the row with the exact string.

2

Select phone_numbers as an array

Write a SELECT query to get the id and convert the phone_numbers string into a text array using the string_to_array function with comma ',' as the delimiter.

PostgreSQL

CREATE TABLE contacts (id INTEGER, phone_numbers TEXT);
INSERT INTO contacts (id, phone_numbers) VALUES (1, '123-456-7890,987-654-3210,555-555-5555');

-- Select id and phone_numbers as an array
-- Your code here

Hint

Use string_to_array(column_name, ',') to split the string into an array.

3

Convert the array back to a string

Write a SELECT query to get the id and convert the array of phone numbers back into a single comma-separated string using the array_to_string function. Use the same array conversion inside the query.

PostgreSQL

CREATE TABLE contacts (id INTEGER, phone_numbers TEXT);
INSERT INTO contacts (id, phone_numbers) VALUES (1, '123-456-7890,987-654-3210,555-555-5555');

-- Select id and convert array back to string
-- Your code here

Hint

Use array_to_string(array, ',') to join array elements back into a string.

4

Complete query with both conversions

Write a SELECT query that shows id, the phone numbers as an array named phone_array, and the phone numbers converted back to a string named phone_string. Use string_to_array and array_to_string functions in the same query.

PostgreSQL

CREATE TABLE contacts (id INTEGER, phone_numbers TEXT);
INSERT INTO contacts (id, phone_numbers) VALUES (1, '123-456-7890,987-654-3210,555-555-5555');

-- Select id, phone_numbers as array and as string
-- Your code here

Hint

Combine both functions in the SELECT clause to show both array and string formats.

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