sql - 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