String to array and array to string in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What PostgreSQL function converts a string into an array?

The string_to_array(text, delimiter) function splits a string into an array using the specified delimiter.

beginner

How do you convert an array back into a string in PostgreSQL?

Use the array_to_string(array, delimiter) function to join array elements into a single string separated by the delimiter.

beginner

Example: What is the result of string_to_array('apple,banana,cherry', ',')?

It returns the array {apple,banana,cherry} splitting the string at each comma.

beginner

Example: What does array_to_string(ARRAY['red', 'green', 'blue'], ';') return?

It returns the string 'red;green;blue' joining array elements with semicolons.

intermediate

Why are string to array and array to string conversions useful in databases?

They help handle lists stored as text, making it easy to split or join values for searching, filtering, or displaying data.

Which function splits a string into an array in PostgreSQL?

A split_string

B array_to_string

C string_to_array

D to_array

The string_to_array function splits a string into an array using a delimiter.

What does array_to_string(ARRAY['a','b','c'], ':') return?

A 'a,b,c'

B 'a;b;c'

C 'abc'

D 'a:b:c'

It joins array elements with ':' resulting in 'a:b:c'.

If you want to split 'dog|cat|bird' into an array, which delimiter should you use?

A |

B :

C ;

D ,

The delimiter '|' matches the separator in the string.

Which function would you use to join array elements into a single string?

A array_to_string

B array_join

C string_to_array

D join_array

The array_to_string function joins array elements into a string.

What is the output of string_to_array('one;two;three', ';')?

A {'one;two;three'}

B {one,two,three}

C 'one;two;three'

D 'one,two,three'

The string is split at each ';' into an array of three elements.

Explain how to convert a comma-separated string into an array in PostgreSQL and why this might be useful.

Think about how you can turn a list in text form into separate pieces.

Describe how to join an array of strings into a single string with a custom separator in PostgreSQL.

Imagine putting pieces of a puzzle back together with glue.