Whenever you need to split a text into multiple records breaking by some delimeter, there are two common options that PostgreSQL provides. The first is regpexp_split_to_table and then next popular is using the unnest function in combination with string_to_array.
Related Articles
Here is an example using regexp_split_to_table:
SELECT a
FROM regexp_split_to_table('john,smith,jones', ',') AS a;
Which outputs:
a ------- john smith jones (3 rows)
You can achieve the same result by using the construct:
SELECT a
FROM unnest(string_to_array('john,smith,jones', ',')) AS a;
With short text you won't notice much perfomance difference. But what happens if we pass in a humungous text?
Continue reading "regexp_split_to_table and string_to_array unnest performance"