How to Split a Column into Multiple Rows in PostgreSQL?

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 WHERE clause is used to filter the results based on the flag value.
• The comma in the FROM list represents a CROSS JOIN, which is automatically assumed for set-returning functions in the FROM list.
• If the subject column can be empty or null, use a LEFT JOIN with ON TRUE to keep qualifying rows from the original table.

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!