PostgreSQLでCSV形式の文字列の各値を更新するSQLです。
例えば、以下のようなデータがあったとして、
id | csv |
---|---|
1 | 1,11,110 |
2 | 11,111 |
csvカラム内の「11」を「22」に変えて
id | csv |
---|---|
1 | 1,22,110 |
2 | 22,111 |
のようなデータに更新するSQLです。
(「110」や「111」は更新されていないのがポイントです)
with t1 as (
select
id,
array_to_string(array_agg(
case sp.c1
when '11' then '22'
else sp.c1
end
), ',') as c2
from (
select id, regexp_split_to_table(csv, ',') as c1 from sample where csv is not null and csv != ''
)
as sp
group by
id
)
update
sample
set
csv = t1.c2
from
t1
where
sample.id = t1.id;
①カンマ区切りデータをregexp_split_to_tableを使って分割し、別レコードで取得
②caseを使用し、値を変換
③idでグループ化し、再度カンマ区切りで結合し、update
補足
- そもそもこのようなテーブル設計は採用すべきではなく、可変データは配列型にしたり別テーブルで管理した方が良いと思います
- パフォーマンスは考慮していないのと、CSVデータの並び順も考慮していないので、更新すると並び順変わるかもしれないです
参考
PostgresSQLでカンマ区切りのデータをwhere で絞り込みたい
複数レコードをカンマ区切りの1レコードにまとめる方法
PostgreSQLのUPDATE文でWITH句とFROM句が使えることにびっくりした。