PostgreSQL にて、テーブル hoges に対して hoge.csv の内容を挿入する方法について調べた。
まとめ
- hoge.csv がデータベースサーバに配置できるなら、COPY コマンドを使用する。
- hoge.csv がクライアントにしか配置できないなら、\copy コマンドを使用する。
また、COPY コマンドを ruby 経由で実行するには、gem postgres-copy を使う。\copy は ruby から普通には実行できない。シェルスクリプトを使ったほうが良いかもしれない。
詳細
COPY コマンド
postgres では COPY コマンドを使って、CSV ファイルのレコードをテーブルに挿入することができる。( http://www.postgresql.jp/document/9.2/html/sql-copy.html ) 対象となるファイル hoge.csv がデータベースサーバに存在するならば、下の SQL を実行することで問題は解決する。なお、COPY コマンドは標準 SQL ではない。ここではCSVファイルのパスを省略しているが、ドキュメントでは絶対パスで指定するのが推奨されている。
COPY hoges (column_names,...) FROM 'hoge.csv' WITH CSV
hoge.csv がデータベースサーバに存在しない場合は、事前に scp 等のコマンドでファイルを転送する必要がある。scp などが利用できず、ファイルを転送できない時には下の方法で、直接データを送ることができる。（ただし、このような場合は次節の \copy を使うほうが楽である）
COPY hoges (column_names,...) FROM STDIN WITH CSV;
-- 続けて hoge.csv のデータを直接記述する
\copy メタコマンド
上の節で説明した方法の省略形として \copy メタコマンドが存在する。こちらのコマンドは hoge.csv にサーバになくても良い。（クライアント側に置く必要がある）
\copy the_table (column_names,...) from 'hoge.csv' with csv
Ruby との連携
Ruby で postgres にアクセスするには pg ( https://github.com/ged/ruby-pg ) という gem を使う。 COPY コマンドを実行するには、pg のコネクションを生成し、 exec メソッドで実行する。
con = PGconn.connect(...)
con.exec("COPY hoges (column_names,...) FROM 'hoge.csv' WITH CSV")
このような処理を隠蔽してモデルから実行する gem postgres-copy ( https://github.com/diogob/postgres-copy ) がある。しかし、残念なことに、メタコマンドは pg を介して行うことができない。（バックスラッシュが現れた時点でシンタックスエラーとなる）つまり、 \copy を使用することはできない。それでも強引にやるとすれば、システムコールから psql を経由して実行する方法がある。
copy_command = %q("\copy hoges (column_names,...) from 'hoge.csv' with csv")
Kernel::exec("psql -c #{copy_command}")
実際には psql にデータベースサーバへの接続情報を与えなければいけないため、より煩雑になる。
CSV ファイルを読みこんでインポートする場合は put_copy_data メソッドを使って、下記のようにする手もあるかもしれない。（注：動作確認していないので、正しくないかもしれません）
conn.copy_data "COPY hoges FROM STDIN CSV" do
CSV.foreach("hoges.csv") do |row|
conn.put_copy_data(row + "\n")
end
end
詳しくは下記ドキュメントを参照。