PostgreSQLでカンマ区切り列をスマートに分割：split_part関数 vs regexp_split_to_table関数

2024-06-16

PostgreSQLでカンマ区切り列データを複数の列に分割する方法

例

以下のテーブル data があり、value 列にはカンマ区切りのデータが含まれています。

CREATE TABLE data (
  id serial PRIMARY KEY,
  value text
);

INSERT INTO data (value) VALUES
  ('name1,age1,city1'),
  ('name2,age2,city2'),
  ('name3,age3,city3');

この value 列を、カンマ区切りで分割して3つの列 (name, age, city) に格納するには、以下のクエリを実行します。

SELECT * FROM regexp_split_to_table(data.value, ',') AS splitted;

このクエリは、以下の結果を生成します。

id | name   | age | city
---+-------+-----+------
1  | name1  | age1 | city1
2  | name2  | age2 | city2
3  | name3  | age3 | city3

オプション

regexp_split_to_table 関数は、区切り文字を指定するオプション引数を取ることができます。例えば、セミコロンで区切られたデータの場合は、以下のように記述します。

SELECT * FROM regexp_split_to_table(data.value, ';') AS splitted;

区切り文字だけでなく、各フィールドの最大長を指定することもできます。これは、固定長のデータ列を作成する場合に役立ちます。

SELECT * FROM regexp_split_to_table(data.value, ',', 10) AS splitted;

複数行の処理

上記の例では、1つの列のデータを分割する方法を説明しました。複数の行のデータを処理するには、FROM 句にサブクエリを使用できます。

SELECT id, name, age, city
FROM data,
     regexp_split_to_table(value, ',') AS splitted
WHERE data.id = splitted.rownum;

注意事項

regexp_split_to_table 関数は、データを新しいテーブルに格納します。既存のテーブルを更新するには、UPDATE または INSERT ステートメントと組み合わせて使用する必要があります。
カンマ区切りのデータには、エスケープされたカンマが含まれている場合があります。そのような場合は、適切な正規表現を使用して文字列を分割する必要があります。

PostgreSQLでカンマ区切り列データを複数の列に分割するサンプルコード

例1：単一の列を分割

この例では、data テーブルの value 列にあるカンマ区切りデータを、name、age、city の3つの列に分割します。

-- テーブルの作成
CREATE TABLE data (
  id serial PRIMARY KEY,
  value text
);

-- データの挿入
INSERT INTO data (value) VALUES
  ('John Doe,30,New York'),
  ('Jane Doe,25,San Francisco'),
  ('Peter Jones,40,Chicago');

-- カンマ区切りデータを分割
SELECT id,
       unnest(regexp_split_to_array(value, ',')) AS vals
FROM data;

id | vals
---+--------------------------------------------------
1  | {'John Doe', '30', 'New York'}
2  | {'Jane Doe', '25', 'San Francisco'}
3  | {'Peter Jones', '40', 'Chicago'}

次に、unnest 関数を使用して、配列を個々の行に変換します。

SELECT id,
       unnest(regexp_split_to_array(value, ',')) AS (name, age, city)
FROM data;

id | name   | age | city
---+-------+-----+------
1  | John Doe | 30  | New York
2  | Jane Doe | 25  | San Francisco
3  | Peter Jones | 40  | Chicago

例2：複数の行を処理

この例では、data テーブルのすべての行を処理し、value 列のデータを分割して新しいテーブル data_split に格納します。

-- テーブルの作成
CREATE TABLE data_split (
  id serial PRIMARY KEY,
  name text,
  age integer,
  city text
);

-- データの挿入
INSERT INTO data (value) VALUES
  ('John Doe,30,New York'),
  ('Jane Doe,25,San Francisco'),
  ('Peter Jones,40,Chicago');

-- カンマ区切りデータを分割して新しいテーブルに挿入
INSERT INTO data_split (id, name, age, city)
SELECT id,
       unnest(regexp_split_to_array(value, ',')) AS vals
FROM data;

このクエリを実行すると、data_split テーブルが作成され、以下のデータが挿入されます。

id | name   | age | city
---+-------+-----+------
1  | John Doe | 30  | New York
2  | Jane Doe | 25  | San Francisco
3  | Peter Jones | 40  | Chicago

説明

regexp_split_to_array 関数は、指定された区切り文字（カンマなど）で文字列を分割し、結果を配列として返します。
INSERT ステートメントを使用して、データを新しいテーブルに挿入します。

区切り文字だけでなく、各フィールドの最大長を指定することもできます。
unnest 関数は、オプションで戻り値の列名を指定できます。

これらのオプションの詳細については、PostgreSQLドキュメントを参照してください。

このサンプルコードが、PostgreSQLでカンマ

PostgreSQLでカンマ区切り列データを分割するその他の方法

split_part 関数は、文字列を指定された区切り文字で分割し、その一部を抽出するものです。カンマ区切り列データを分割する場合、以下のクエリのように使用できます。

SELECT
  id,
  split_part(value, ',', 1) AS name,
  split_part(value, ',', 2) AS age,
  split_part(value, ',', 3) AS city
FROM data;

利点

シンプルで分かりやすい構文
複数の列を分割する際に便利

欠点

分割する列の数が固定である
区切り文字がエスケープされている場合に処理が複雑になる

サブクエリを使用して、ROW_NUMBER() 関数と unnest 関数を組み合わせて、カンマ区切り列データを分割することもできます。

SELECT id,
       unnest(array(
         SELECT value || ','
         FROM data
         ORDER BY id
         LIMIT 1 OFFSET rownum - 1
       )) AS vals
FROM data,
     ROW_NUMBER() OVER (ORDER BY id) AS rownum;