How to Split a Column of Text into Multiple Rows in Postgres?

Splitting Columns into Multiple Rows in Postgres

Given a table with a column containing text and another column containing integer flags, there may be a need to transform the table into a new format where each word in the text column becomes a separate row in the new table, while maintaining the corresponding flag values. This article explores how to achieve this using Postgres.

One effective method is to utilize a LATERAL join in combination with the string_to_table() function in Postgres 14 or later. The LATERAL keyword is applied to the join to create a set-returning function (SRF) that returns multiple rows for each row in the parent table. The string_to_table() function splits the text column into individual words, creating a new row for each word.

For instance, consider the following query:

SELECT token, flag
FROM tbl, string_to_table(subject, ' ') AS token
WHERE flag = 2;

This query performs a LATERAL join between the tbl table and the SRF string_to_table(subject, ' '), which tokenizes the subject column by splitting it on white space. The WHERE clause filters the results to include rows where the flag column is equal to 2. As a result, the output table will have one row for each word in the subject column, with the corresponding flag values.

In Postgres 13 or older, unnest(string_to_array(subject, ' ')) can be used instead of string_to_table(subject, ' ').

The above is the detailed content of How to Split a Column of Text into Multiple Rows in Postgres?. For more information, please follow other related articles on the PHP Chinese website!