Split column into multiple rows in Postgres

Question

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?

Answer 1

In Postgres 9.3+ use a LATERAL join:

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

It's an implicit LATERAL join. If unnest() does not return any rows (empty or NULL subject), the result will be no row at all. Use LEFT JOIN unnest(...) i ON true to always return rows from tbl. See:

• What is the difference between LATERAL JOIN and a subquery in PostgreSQL?

You could also use regexp_split_to_table(), but that's typically slower because regular expression matching costs a bit more. Related:

• SQL select rows containing substring in text field
• PostgreSQL unnest() with element number

Answer 2

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                                                                                                   

Answer 3

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