postgresql分割字符串_拆分Postgresql中的逗号分隔字段，并对所有生成的表执行UNION ALL...

I have a table that contains a field of comma separated strings:

ID | fruits

-----------

1 | cherry,apple,grape

2 | apple,orange,peach

I want to create a normalized version of the table, like this:

ID | fruits

-----------

1 | cherry

1 | apple

1 | grape

2 | apple

2 | orange

2 | peach

The postgresql 8.4 documentation describes a regexp_split_to_table function that can turn a single table:

SELECT foo

FROM regexp_split_to_table('the quick brown fox jumped over the lazy dog',E'\\s+') AS

foo;

which gives you this:

foo

--------

the

quick

brown

fox

jumped

over

the

lazy

dog

(9 rows)

But that is just for a single field. What I want to do is some kind UNION applied to all the tables generated by splitting each field. Thank you.

解决方案

This should give you the output you're looking for:

SELECT

yourTable.ID,

regexp_split_to_table(yourTable.fruits, E',') AS split_fruits

FROM yourTable

EDIT: Fixed the regex.