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?

Sigismund

Answer 1

Use a LATERAL join - with string_to_table() in Postgres 14+.
Minimal form:

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

The comma in the FROM list is (almost) equivalent to CROSS JOIN, LATERAL is automatically assumed for set-returning functions (SRF) in the FROM list. Why "almost"? See:

• "invalid reference to FROM-clause entry for table" in Postgres query

The alias "token" for the derived table is also assumed as column alias for a single anonymous column, and we assumed distinct column names across the query. Equivalent, more verbose and less error-prone:

SELECT s.token, t.flag
FROM   tbl t
CROSS  JOIN LATERAL string_to_table(subject, ' ') AS s(token)
WHERE  t.flag = 2;

Or move the SRF to the SELECT list, which is allowed in Postgres (but not in standard SQL), to (almost) the same effect:

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

The last one seems acceptable since SRF in the SELECT list have been sanitized in Postgres 10. See:

• What is the expected behaviour for multiple set-returning functions in SELECT clause?

If string_to_table() does not return any rows (empty or null subject), the (implicit) join eliminates the row from the result. Use LEFT JOIN ... ON true to keep qualifying rows from tbl. See:

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

We could also use regexp_split_to_table(), but that's slower. Regular expressions are powerful but expensive. See:

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

In Postgres 13 or older use unnest(string_to_array(subject, ' ')) instead of string_to_table(subject, ' ').

Kaoliang

I’m not very familiar with either the LATERAL join or with the unnest() function. How would you express this as a lateral join? – Voidance

@Manngo: This is a lateral join, just with short syntax. Verbose equivalent: SELECT * FROM tbl t CROSS JOIN LATERAL unnest(string_to_array(t.subject, ' ')) AS s(token); Ample explanation in the linked answers. – Kaoliang

Thanks. I always understood the … , … syntax to be a simple cross join. – Voidance

For functions, it's cross join lateral automatically. – Kaoliang

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                                                                                                   

Brandwein

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

Dorcia