PostgreSQL にて、テーブル hoges に対して hoge.csv の内容を挿入する方法について調べた。

まとめ

1. hoge.csv がデータベースサーバに配置できるなら、COPY コマンドを使用する。
2. 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

詳しくは下記ドキュメントを参照。