Splitting Column into Multiple Rows in Postgres
Suppose you have a table with a column containing multiple values, such as:
subject | flag --------+----- this is a test | 2
You may need to transform this table into a new structure where each value from the original column becomes a separate row, like this:
token | flag --------+----- this | 2 is | 2 a | 2 test | 2
Solution with LATERAL Join (Postgres 14 )
Postgres 14 and later provide an efficient way to achieve this using a LATERAL join with the string_to_table() function. This function splits the subject column into an array of tokens:
SELECT token, flag FROM tbl, string_to_table(subject, ' ') AS token WHERE flag = 2;
Solution with string_to_array() (Postgres 13-)
For Postgres 13 or earlier, use the string_to_array() function instead of string_to_table():
SELECT unnest(string_to_array(subject, ' ')) AS token, flag FROM tbl WHERE flag = 2;
Alternative Solution with Regexp
Another option is to use the regexp_split_to_table() function, although it is less efficient than the string_to_table() approach:
SELECT s.token, t.flag FROM tbl t CROSS JOIN regexp_split_to_table(subject, ' ') AS s(token) WHERE t.flag = 2;
Additional Notes
The above is the detailed content of How to Split a Column into Multiple Rows in PostgreSQL?. For more information, please follow other related articles on the PHP Chinese website!