データベース city
ユーザー scott
とします。
テーブルの状況
$ psql -U scott city
psql (16.3 (Ubuntu 16.3-0ubuntu0.24.04.1), server 14.8 (Ubuntu 14.8-0ubuntu0.22.10.1))
Type "help" for help.
city=# select * from cities;
id | name | population | date_mod
-------+--------+------------+------------
t3461 | 広島 | 25176 | 2003-09-24
t3462 | 福山 | 47935 | 2003-05-15
t3463 | 東広島 | 28654 | 2003-02-08
t3464 | 呉 | 83152 | 2003-10-09
t3465 | 尾道 | 42791 | 2003-08-04
t3466 | 竹原 | 35687 | 2003-01-21
t3467 | 三次 | 81296 | 2003-07-23
t3468 | 大竹 | 21764 | 2003-10-26
t3469 | 府中 | 75423 | 2003-12-15
(9 rows)
city=#
export
export.sql
copy public.cities to '/tmp/cities.csv' with csv header force quote *;
実行コマンド
psql -U scott city < export.sql
実行結果
$ psql -U scott city < export.sql
COPY 9
作成されたファイル
/tmp/cities.csv
d,name,population,date_mod
"t3461","広島","25176","2003-09-24"
"t3462","福山","47935","2003-05-15"
"t3463","東広島","28654","2003-02-08"
"t3464","呉","83152","2003-10-09"
"t3465","尾道","42791","2003-08-04"
"t3466","竹原","35687","2003-01-21"
"t3467","三次","81296","2003-07-23"
"t3468","大竹","21764","2003-10-26"
"t3469","府中","75423","2003-12-15"
テーブル内のデータを消去
city=# delete from cities;
DELETE 9
消去されたことを確認
city=# select * from cities;
id | name | population | date_mod
----+------+------------+----------
(0 rows)
import
import.sql
copy public.cities from '/tmp/cities.csv' with csv header;
実行コマンド
psql -U scott city < import.sql
実行結果
$ psql -U scott city < import.sql
COPY 9
結果の確認
city=# select * from cities;
id | name | population | date_mod
-------+--------+------------+------------
t3461 | 広島 | 25176 | 2003-09-24
t3462 | 福山 | 47935 | 2003-05-15
t3463 | 東広島 | 28654 | 2003-02-08
t3464 | 呉 | 83152 | 2003-10-09
t3465 | 尾道 | 42791 | 2003-08-04
t3466 | 竹原 | 35687 | 2003-01-21
t3467 | 三次 | 81296 | 2003-07-23
t3468 | 大竹 | 21764 | 2003-10-26
t3469 | 府中 | 75423 | 2003-12-15
(9 rows)
テーブルの定義とコラムの順が異なる csv ファイルの入力
入力の csv ファイル
/tmp/cities2.csv
id,name,date_mod,population
"t3461","広島","2003-09-24","25176"
"t3462","福山","2003-05-15","47935"
"t3463","東広島","2003-02-08","28654"
"t3464","呉","2003-10-09","83152"
"t3465","尾道","2003-08-04","42791"
"t3466","竹原","2003-01-21","35687"
"t3467","三次","2003-07-23","81296"
"t3468","大竹","2003-10-26","21764"
"t3469","府中","2003-12-15","15423"
import2.sql
COPY public.cities (id, name, date_mod, population)
from '/tmp/cities2.csv' with csv header;
実行コマンド
psql -U scott city < import2.sql