postgresでカンマ区切りのデータをばらしてほかのテーブルと結合したい

なんかもうタイトルから設計ミスのにおいがプンプンしますが最近ぶち当たったので備忘録。

fruit_csv

id	fruit_names
1	りんご,すいか,もも
fruit

id	fruit_name
1	りんご
2	すいか
3	みかん
4	もも

こういう二つのテーブルがあったとして、fruit_csv.fruit_namesをばらしてfruitからidを取得したい。

select
    fruit.id,
    t.fruit_name
from (
    select
        id,
        regexp_split_to_table(fruit_names, ',') as fruit_name
    from fruit_csv
) t
inner join fruit
    on t.fruit_name = fruit.fruit_name
;

regexp_split_to_table(カラム名, ',')で指定してあげてください。
また、サブクエリにしてあげないと条件を付けられないので注意してください。
WHERE句などの絞り込みも同様ということです。

id	fruit_name
1	りんご
2	すいか
4	もも

こんな感じでとってこれました。
カンマ区切りでfruit.idを返してほしいな～って思ったら
WITH句を使います。

with main as (
    select
        t.id,
        fruit.id as fruit_id
    from (
        select
            id,
            regexp_split_to_table(fruit_names, ',') as fruit_name
        from fruit_csv
    ) t
    inner join fruit
        on t.fruit_name = fruit.fruit_name
)
select
    distinct id,
    array_to_string(
        array(
            select main.fruit_id
            from main
        ),
    ',') as fruit_id
from main;

id	fruit_id
1	1,2,4

でも正直こんなことしないでテーブルを変えるなり何か別な方法を取ったほうがいいと思います。
書いてから思いましたけどIDから名前とってくる例のほうがよかったですね。。まあいいか…