How to Split a Text Column into Multiple Rows in PostgreSQL?

Splitting Columns into Rows in Postgres

In Postgres, the need may arise to transform a table with a single column of type text into a table with multiple rows, where each row represents a token from the text column.

Sample Data and Desired Output

Consider the following source table:

</p>
<table>
<thead><tr>
<th>subject</th>
<th>flag</th>
</tr></thead>
<tbody><tr>
<td>this is a test</td>
<td>2</td>
</tr></tbody>
</table>
<p>

The desired output is a table with two columns:

</p>
<table>
<thead><tr>
<th>token</th>
<th>flag</th>
</tr></thead>
<tbody>
<tr>
<td>this</td>
<td>2</td>
</tr>
<tr>
<td>is</td>
<td>2</td>
</tr>
<tr>
<td>a</td>
<td>2</td>
</tr>
<tr>
<td>test</td>
<td>2</td>
</tr>
</tbody>
</table>
<p>

Solution Using LATERAL Join

For Postgres 14 and above, the LATERAL join can be used with the string_to_table() function to achieve this transformation. The minimal form of the query is:

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

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

The string_to_table() function splits the subject column into separate tokens, one for each row, with the same value for the flag column. The WHERE clause filters the results to only show tokens flagged with a value of 2.

This solution provides a concise and efficient way to split a text column into individual rows in Postgres.

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