PostgreSQLで1回以上連続するスペースで文字列を配列に分割する

不確定回連続する記号をデリミッタに用いる

UNIXのコマンドラインの文字列を取り敢えず1列のテキスト型のカラムとして挿入し、PostgreSQLの内部でSQLで、要素ごとに分割するための記事です。

例えば、ls -l コマンドでファイル名とファイル容量を取得する場合、データとデータの間に不特定かつ複数回のスペースが連続しstring_to_array関数では対応出来ない場合が有ります。

lslog.txt

-rw-rw-r-- 1 root root    319  9月 29  2010 hoge.txt
-rw-rw-r-- 1 root root   2543  9月 30  2010 huga.txt

上記1行目のrootと319の間にはスペースが4つ、2行目のrootと2543の間にはスペースが3つ出現します。これをどちらも1つのデリミッタとして分割するのが今回の目標です。

regexp_split_to_array関数

PostgreSQL 8.3から導入されたregexp_split_to_array関数は、文字列を正規表現で定義されたデリミッタを用いて配列化する関数です。

複数回のスペースを配列化する正規表現は、ドュキュメント内に記述例があります

regexp_split_to_array

SELECT 
 regexp_split_to_array( line , E'\\s+')
FROM lslog

実行結果

{-rw-rw-r--,1,root,root,319,9月,29,2010,hoge.txt}
{-rw-rw-r--,1,root,root,2543,9月,30,2010,huga.txt}

スペースが連続しても、それを1つのデリミッタとして用いられていることが確認できました。

E'\\s+'の意味

regexp_split_to_arrayの第2引数のE'\\s+'には、文字列中のバックスラッシュの取り扱いをE''と記述することにより、エスケープ文字列として取り扱うことを宣言しています。また正規表現では、\\s（スペース記号）が+（1回以上）連続する文字列をデリミッタとして用いる事を表現しています。