In PostgreSQL, string_to_array() is a string function that allows us to create an array from a string. It splits the string based on the specified delimiter and returns a text array as a result.

If we specify a null delimiter, then each character becomes an element in the array. If the delimiter string is empty, then the whole string becomes a single element in the array.

We also have the option of turning a specific substring into null if required.

Example

Here’s an example to demonstrate the function:

SELECT string_to_array('Cat-Dog-Bird', '-');

Result:

{Cat,Dog,Bird}

In that example a hyphen was the separator. Here’s one where a space is the separator:

SELECT string_to_array('Cat Dog Bird', ' ');

Result:

{Cat,Dog,Bird}

We can also specify multiple characters as the separator:

SELECT string_to_array('Cat, Dog, Bird', ', ');

Result:

{Cat,Dog,Bird}

In this example I specified that a comma followed by a space is the separator.

Passing a null Separator

Specifying null for the separator results in each character being a separate element in the array:

SELECT string_to_array('Cat-Dog-Bird', null);

Result:

{C,a,t,-,D,o,g,-,B,i,r,d}

Passing an Empty String as a Separator

Specifying an empty string as the separator results in the whole string being treated as a single element in the array:

SELECT string_to_array('Cat-Dog-Bird', '');

Result:

{Cat-Dog-Bird}

Converting a Substring to null

We can pass an optional third argument to specify that a certain substring is output as null in the resulting array:

SELECT string_to_array('Cat-Dog-Bird', '-', 'Dog');

Result:

{Cat,NULL,Bird}

But if the specified value doesn’t exist in the string, then it has no effect on the resulting array:

SELECT string_to_array('Cat-Dog-Bird', '-', 'Bull');

Result:

{Cat,Dog,Bird}

Creating a Multi Dimensional Array

We can pass string_to_array() to the ARRAY constructor in order to create a multi dimensional array:

SELECT ARRAY[string_to_array('Cat-Dog-Bird', '-')];

Result:

{{Cat,Dog,Bird}}

Here it is with multiple calls to string_to_array():

SELECT ARRAY[string_to_array('Cat-Dog-Bird', '-'),string_to_array('Bull, Rat, Ant', ', ')];

Result:

{{Cat,Dog,Bird},{Bull,Rat,Ant}}