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

Need a 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

Need a 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

Need a 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

Need a hint?

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