I have a Postgres table where the content of a text column is delimited with '|'.
ID | ... | my_column
-----------------------
1 | ... | text|concatenated|as|such
2 | ... | NULL
3 | ... | NULL
I tried to unnest(string_to_array()) this column to separate rows which works fine, except that my NULL values (>90% of all entries) are excluded. I have tried several approaches:
SELECT * from "my_table", lateral unnest(CASE WHEN "this_column" is NULL
THEN NULL else string_to_array("this_column", '|') END);
or
as suggested here: PostgreSQL unnest with empty array
What I get:
ID | ... | my_column
-----------------------
1 | ... | text
1 | ... | concatenated
1 | ... | as
1 | ... | such
But this is what I need:
ID | ... | my_column
-----------------------
1 | ... | text
1 | ... | concatenated
1 | ... | as
1 | ... | such
2 | ... | NULL
3 | ... | NULL
Use a LEFT JOIN instead:
SELECT m.id, t.*
from my_table m
left join lateral unnest(string_to_array(my_column, '|')) as t(w) on true;
There is no need for the CASE statement to handle NULL values. string_to_array will handle them correctly.
Online example: http://rextester.com/XIGXP80374