【PostgreSQL】ファイルからSQL文を一括で実行するには ～pdqlコマンドの利用～

PostgreSQL Shell

■ はじめに

 * 開発環境設定やテストデータ作成の際に
   いちいち作ったりするのが面倒なので、
   ファイルにして残しておけば、自分だけじゃなく
   別の開発メンバーにも役立つので
   ファイルからSQL文を一括で実行する方法を調べてみた。

補足：pdql コマンド

以下の関連記事を参照のこと

https://dk521123.hatenablog.com/entry/2020/01/10/224818

 ■ ファイルからSQL文を一括で実行するには

* psqlコマンドを利用する

 構文

括弧（）はオプション 

psql -f 【実行したいSQL文が書かれているファイル】 -U 【Postgreユーザ名】 （【DB名】）（-h 【ホスト名】）
Password: [【Postgreユーザ名】のパスワードを入力する]

■ サンプル

例１：一括で実行したいSQL文

 initDb.sql 

DROP DATABASE IF EXISTS sampledb;

DROP USER IF EXISTS admin;
CREATE USER admin WITH PASSWORD 'admin' CREATEDB;

CREATE DATABASE sampledb
  WITH OWNER = admin
       ENCODING = 'UTF8';
\c sampledb admin

DROP TABLE IF EXISTS person;

CREATE TABLE person
(
  id character(8) NOT NULL,
  name character varying(100),
  sex character(1),
  updatedate timestamp without time zone,
  CONSTRAINT person_pkey PRIMARY KEY (id)
);

ALTER TABLE person
  OWNER TO admin;

INSERT INTO person(
            id, name, sex, updatedate)
    VALUES ('X0000000', 'admin', 'm', Now()),
           ('X0000001', 'Mike', 'm', Now()),
           ('X0000002', 'Tom', 'm', Now());

 実行コマンド 

#「-h localhost」はローカルなので今回はなくてもいい 
psql -f initDb.sql -U postgres -h localhost

 出力結果 

DROP DATABASE
CREATE ROLE
CREATE DATABASE
SSL 接続 (暗号化方式: DHE-RSA-AES256-SHA, ビット長: 256)
データベース "sampledb" にユーザ"postgres"として接続しました。
DROP TABLE
psql:initDb.sql:20: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "person_pkey" for table "person"
CREATE TABLE
ALTER TABLE
INSERT 0 3

【おまけ】SQLファイルを一括で実行するシェル

階層に以下のようなSQLファイルがあった場合に、
それを一括で実行するシェルを作成する

delete_insert_【テーブル名】.sql

なお、シェルには、以下の関連記事で扱ったことを使う

https://dk521123.hatenablog.com/entry/2020/01/10/224818
https://dk521123.hatenablog.com/entry/2019/11/23/101625
https://dk521123.hatenablog.com/entry/2015/02/20/001600
シェル (sample.sh) 

#!/bin/sh

for file in `ls *.sql`;
do
  echo "Result = ${file}."
  psql -f "./${file}" -U postgres -d sampledb -h localhost

  table_name=`echo ${file} | sed -r 's/delete_insert_(.*).sql$/\1/'`
  echo "Table Name = ${table_name}."
  psql -C "SELECT * FROM ${table_name};" -A -U postgres -d sampledb -h localhost
done

実行コマンド 

./sample.sh

 参考文献

 * 非常に参考になった。感謝。

http://www.develop-memo.com/database/postgresql/postgresqloperate.html
 データベースを選択する : \c データベース名
http://jyukutyo.hatenablog.com/entry/20090208/1234164272  テーブルが存在したら削除(DROP)する : DROP TABLE IF EXISTS テーブル名
http://www.dbonline.jp/mysql/table/index4.html
 ユーザ追加 CREATE USER ユーザ名 WITH PASSWORD 'パスワード' CREATEDB;
https://www.postgresql.jp/document/8.0/html/sql-createuser.html
 ユーザ削除 / DROP USER ユーザ名
https://www.postgresql.jp/document/pg702doc/user/sql-dropuser.htm

 関連記事

 Ant で、DB を 初期化する ～ PostgreSQL編 ～
https://dk521123.hatenablog.com/entry/35015658
よく使う pdqlコマンド
https://dk521123.hatenablog.com/entry/2020/01/10/224818
psql で 変数 / 定数を使う
https://dk521123.hatenablog.com/entry/2020/01/23/223756
psql コマンドでファイル実行した際に引数を渡すには
https://dk521123.hatenablog.com/entry/2021/08/01/000000