Split Column into Multiple Rows in Postgres

Problem Statement

Consider the following Postgres table:

subject |  flag
--------- ------
this is a test | 2

The goal is to transform this table into a new table with each word in the "subject" column being a new row, while preserving the corresponding "flag" value.

Solution

One effective way to achieve this in Postgres is by employing a LATERAL join in conjunction with string_to_table() (available in Postgres 14 ). This allows us to split the "subject" column into individual tokens and join them with the original table.

The following query accomplishes this transformation:

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

In this query, we use the LATERAL join to generate a set of rows for each token in the "subject" column, and we filter the results based on the specified "flag" value.

Alternative Approaches

For Postgres 13 and earlier, unnest(string_to_array(subject, ' ')) can be used instead of string_to_table(subject, ' ').

Another approach is to use the regexp_split_to_table() function, which allows for more flexible tokenization based on regular expressions. However, this approach is generally slower than using string_to_table().