Home > Database > Mysql Tutorial > How to Split a Column into Multiple Rows in PostgreSQL?

How to Split a Column into Multiple Rows in PostgreSQL?

Mary-Kate Olsen
Release: 2025-01-06 21:54:44
Original
798 people have browsed it

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
Copy after login

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
Copy after login

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;
Copy after login

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;
Copy after login

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;
Copy after login

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!

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template