'Split column into multiple rows in Postgres

Suppose I have a table like this:

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

subject is of type text, and flag is of type int. I would like to transform this table to something like this in Postgres:

    token       | flag
----------------+------
 this           |    2
 is             |    2
 a              |    2
 test           |    2

Is there an easy way to do this?

sqlpostgresqlsplitset-returning-functions


Solution 1:[1]

I think it's not necessary to use a join, just the unnest() function in conjunction with string_to_array() should do it:

SELECT unnest(string_to_array(subject, ' ')) as "token", flag FROM test;

token | flag                                                                                                   
-------+-------                                                                                                  
this   |     2                                                                                                   
is     |     2                                                                                                   
a      |     2                                                                                                   
test   |     2

Solution 2:[2]

Using regex split to table function including lateral join,

SELECT s.token, flag
FROM   tbl t, regexp_split_to_table(t.subject, ' ') s(token)
WHERE  flag = 2;

Refer to https://www.postgresql.org/docs/9.3/functions-string.html for the function details

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 user27137772

Related Questions