Postgres: String to Rows

(Last Updated On: )

In this tutorial I will show you how to convert a string to rows delimited by a character.

There are three ways to do this all lead to the same answer.

  1. --METHOD 1
  2. SELECT split_data FROM regexp_split_to_table('I love programming!', E' ') AS split_data;
  3.  
  4. --METHOD 2
  5. SELECT split_data
  6. FROM unnest(string_to_array('I love programming!',' ')) AS split_data;
  7.  
  8. --METHOD 3
  9. SELECT split_string.arr[i] as split_data
  10. FROM (
  11. SELECT generate_series(1, array_upper(arr, 1)) AS i, arr
  12. FROM (SELECT ARRAY['I','love','programming!'] arr) t
  13. ) split_string;
Results:

Each executes in 11ms based on my analysis. Preferably I would go with “regexp_split_to_table”