PostgreSQLでテーブルをコピーする方法
PostgreSQLでテーブルをコピーする場合、同DB内でのコピーか別DBへのコピーかでやり方が異なる。
DB内のコピーであればSQLだけで可能
テーブル内のデータに加え制約、インデックス、権限等もコピーするには下記のようなSQLを実行すれば良い。
-- コピー元テーブルの定義、制約、インデックス等も含む空テーブルを作成する
CREATE TABLE 先スキーマ.テーブル ( LIKE 元スキーマ.テーブル INCLUDING ALL );
-- 作成した空テーブルの所有者を変更する
ALTER TABLE 先スキーマ.テーブル OWNER TO ユーザー;
-- 上記CREATE文では付与されない権限を付与する
GRANT 権限 ON TABLE 先スキーマ.テーブル TO ユーザー;
-- 元テーブルから先テーブルにデータをコピーする
INSERT INTO 先スキーマ.テーブル SELECT * FROM 元スキーマ.テーブル;
ちなみに下記のAS句でのコピーは型とデータのみをコピーするため主キー制約や非ヌル制約などの制約、またインデックスなどは作成されない。
CREATE TABLE 先スキーマ.テーブル AS SELECT * FROM 元スキーマ.テーブル;
DB間のコピーはダンプ、リストアが必要
あるDBのテーブルを別のDBにコピーする場合、上記のSQLでは対応できないのでいったんテキスト形式でダンプする必要がある。
下記のコマンドでダンプが可能である。
pg_dump -h ホスト -p ポート -U ユーザー -F p -v -t 元スキーマ.テーブル -f ダンプファイル 元データベース
コピー元DBから対象テーブルを指定してテキスト形式の平文SQLファイルとしてダンプする。
出力されるダンプファイルの内容は下記の通りである。
| SQL | 内容 |
|---|---|
| CREATE TABE ～ | テーブル作成 |
| ALTER TABLE ～ OWNER TO ～ | テーブル所有者の変更 |
| COPY ～ | データのコピー |
| ALTER TABLE ～ ADD CONSTRAINT ～ | 制約の付与 |
| CREATE INDEX ～ | インデックスの作成 |
| REVOKE ～ | 権限の削除 |
| GRANT ～ | 権限の付与 |
このダンプファイルのSQLにはスキーマ名修飾がないので適宜直す必要がある。
リストアは下記のpsqlコマンドで対象DBに対してSQLファイルを実行すれば良い。
psql -h ホスト -p ポート -U ユーザー -f ダンプファイル -d 先データベース