Postgres 10 lateral unnest missing null values

Question

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

user330315 · Accepted Answer · 2018-09-11 17:08:31Z

7

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

answered Sep 11, 2018 at 17:08

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

Annegret Over a year ago

Great! What a quick answer. Thank you

Collectives™ on Stack Overflow

Postgres 10 lateral unnest missing null values

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related