7
7

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

RDS PostgreSQLへのcsvファイルを使ったコピーは\copyじゃできない?

Last updated at Posted at 2017-12-22

結論

  • psql database_nameでログインしない
  • psqlコマンドの-cオプション使ってSQLコマンドを実行
  • stdin<を使ってcsvデータを標準出力する
  • カラム名は書く
  • COPYにする(\は付けない)
$ psql table_name -h host_name -c "COPY shop_sales (shop_id,sales_date,shop_name) from stdin with csv DELIMITER ','" < /home/user/hoge.csv

当初の想定

  • psql database_nameでログインして\copyコマンドを実行
  • ファイルはEC2に置いて、そのファイルパスを絶対パスで記述(/home/user_name/hoge.csv)

色々試してみたコマンド集(全部エラー)

=# \copy shop_sales (shop_id,sales_date,shop_name) from ‘/home/user/hoge.csv' with csv;

=# \copy shop_sales (shop_id,sales_date,shop_name) from ‘/home/user/hoge.csv' with DELIMITER ',';

=# \copy shop_sales from ‘/home/user/hoge.csv' with DELIMITER ',';

$ psql table_name -h host_name -c "\copy shop_sales from ‘/home/user/hoge.csv' with csv DELIMITER ','";

# csvの形式とテーブルの形式が合ってない エラー
$ psql table_name -h host_name -c "COPY shop_sales from stdin with csv DELIMITER ','" < /home/user/hoge.csv

awsのドキュメントにも\copyコマンドを使う例が書いてあるのだけど、\copy使えば出来そうなんだけど...
http://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/UserGuide/PostgreSQL.Procedural.Importing.html#PostgreSQL.Procedural.Importing.Copy

まとめ

アプリが何か制御してたりするのかな...
デフォルトでどういう動きになるのかやってみたいなー

参考にさせて頂いたサイト
https://teratail.com/questions/49888

7
7
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
7
7

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?