【PostgreSQL】個人用メモ
Windows機でPostgreSQLを利用して、テーブルを定義したSQL文が書かれたテキストファイルを読み込み、そのテーブルにcsvファイルからデータをインポートする流れをまとめた。
環境（簡易）は以下のとおり。
OS: Windows 10 64bit
PostgreSQL10.3
Windows版だと、「スタート」メニューから「プログラム」→「PostgreSQL10.3」→「SQL Shell(psql)」を選択し、ユーザーやデータベースなどを選択することで指定したデータベースに接続した状態になり、直接SQLを実行できるようになる。
これ以外にもコマンドプロンプトからpsqlコマンドを実行する方法がある。この場合、パスをあらかじめ通しておく必要がある。パスの通し方は以下のブログが詳しい。
コマンドプロンプトから以下のpsqlコマンドを使用するとSQLが書かれた外部ファイルを読み込むことができる。
$ psql -U username -d databasename -f hogehoge.sql
管理者権限でコマンドプロンプトを開き、以下のコマンドを入力する。
$ psql -U postgres -d postgres -f C:\Users\admin\data\hoge.sql
DROP TABLE IF EXISTS work_reserve_tb; CREATE TABLE work_reserve_tb ( reserve_id TEXT NOT NULL, hotel_id TEXT NOT NULL, customer_id TEXT NOT NULL, reserve_datetime TIMESTAMP NOT NULL, checkin_date DATE NOT NULL, checkin_time TEXT NOT NULL, checkout_date DATE NOT NULL, people_num INTEGER NOT NULL, total_price INTEGER NOT NULL, PRIMARY KEY(reserve_id) );
work_reserve_tbが作成されたので、これを削除する。
$ psql -U postgres -d postgres -c "DROP TABLE work_reserve_tb"
次に、インタラクティブシェルからクエリが書かれた外部ファイルを読み込む方法について。外部ファイルは先ほどと同じパスに置いてある。
postgres=# \i C:\Users\admin\data\hoge.sql
これだとC::Parmission denied と表示されてしまう。この点については以下のような記載あり。
bytes.com
以下のようにパスを入力するとうまくいく。
postgres=# \i 'C:\\Users\\admin\\data\\hoge.sql'
CSVファイルの読み込みについて
SQLコマンドが書かれたテキストファイルを読み込みテーブルを作成した後に、csvファイルのデータをテーブルに読み込ませるにはCOPYコマンドを実行する。
postgres=# COPY tablename FROM 'csvファイルの絶対パス' WITH CSV HEADER;
csvファイルの１行目はカラム名なのでHEADERオプションをつけて読み込ませない。読み込むファイルの形式がcsvなのでCSVをつける。
COPYコマンドは、スーパーユーザー権限がないと利用できず、ファイルはPostgreSQLサーバー側での管理となる。具体的にはスーパーユーザーpostgresのもと、work_reserve_tbテーブルがあるデータベースpostgresに接続した状態で、以下のようにCOPYコマンドを実行する。
postgres=# COPY work_reserve_tb FROM 'C:\Program Files\PostgreSQL\10\bin\reserve.csv' WITH CSV HEADER;
うまくデータがテーブルに読み込めているかを確認するため、以下のクエリを書いてみる。
postgres=# SELECT * FROM work_reserve_tb LIMIT 10;
次に、csvファイルをクライアント側に置いた場合に￥copyコマンドからデータのインポートを実行してみる。そのため、まず、テーブルwork_reserve_tbに読み込んだデータをTRUNCATEコマンドで削除する。
postgres=# TRUNCATE work_reserve_tb;
\copyコマンドは以下のとおり。
\copy tablename FROM file path WITH CSV HEADER;
先ほどと異なり、ファイルをクライアント側において以下のように実行。
postgres=# \copy work_reserve_tb FROM C:\Users\hoge\Desktop\awesomebook_code\data\reserve.csv WITH CSV HEADER;
psqlコマンドからだと以下のようになる。
$ psql -U postgres -d postgres -c "\copy work_reserve_tb FROM C:\Users\hoge\Desktop\awesomebook_code\data\reserve.csv WITH CSV HEADER;"
これで、csvファイルのデータをテーブルに読み込むことができた。
ダンプとリストアについて
以下の記事が詳しい。
wp.tech-style.info
試しに、以下のコードでデスクトップにダンプファイルを作成してみる。
C:\WINDOWS\system32> pg_dump -U postgres -Fc -d postgres > C:\Users\admin\Desktop\test.dump
このコマンドによりデスクトップにtest.dumpファイルが保存されている。このファイルをPostgreSQLを新たにインストールしたPCにリストアする。
リストアには以下のコマンドをコマンドプロンプトから実行する（test.dumpはデスクトップに置いてあるとする）。
C:\WINDOWS\system32> pg_restore -U postgres -d postgres C:\Users\admin\Desktop\test.dump
