CREATE TABLE AS ... SELECT ...でシンプルに復元

2024-05-18

PostgreSQLでパブリックから別のスキーマへテーブルを移動する方法

前提条件

PostgreSQL 9.1以降を使用していること
移動先のスキーマが存在すること
テーブルに対する十分な権限を持っていること

手順

以下のコマンドを実行して、テーブルを移動先のスキーマに設定します。

ALTER TABLE table_name SET SCHEMA target_schema;

table_name は移動対象のテーブル名に置き換えます。
target_schema は移動先のスキーマ名に置き換えます。

オプションで、テーブルに関連するプロジェクションを移動します。

デフォルトでは、対象テーブルに紐づく全てのプロジェクションが移動先のスキーマに移動されます。移動先のスキーマ内に存在するプロジェクションのみを移動したい場合は、以下のオプションを使用します。

ALTER TABLE table_name SET SCHEMA target_schema RESTRICT REFERENCES;

例

以下の例では、mytableというテーブルをpublicスキーマからmyschemaスキーマへ移動します。

ALTER TABLE mytable SET SCHEMA myschema;

注意事項

テーブルを移動すると、テーブル所有者も移動先のスキーマ所有者となります。
テーブルに関連する制約、インデックス、トリガーなどのオブジェクトは、自動的に移動先のスキーマへ移動されません。これらのオブジェクトを手動で移動する必要がある場合は、事前に適切なスキーマへ作成しておく必要があります。
PostgreSQL 9.1より前のバージョンでは、ALTER TABLEコマンドでスキーマを変更することはできません。これらのバージョンでは、DROP TABLEコマンドとCREATE TABLEコマンドを使用して、テーブルを別のスキーマに再作成する必要があります。

PostgreSQLでパブリックから別のスキーマへテーブルを移動する - サンプルコード

-- 1. テーブルをmyschemaスキーマに移動する
ALTER TABLE mytable SET SCHEMA myschema;

-- 2. オプションで、テーブルに関連するプロジェクションを移動する
-- (デフォルトでは、全てのプロジェクションが移動されます)
ALTER TABLE mytable SET SCHEMA myschema RESTRICT REFERENCES;

説明

最初のALTER TABLEコマンドは、mytableテーブルをmyschemaスキーマに移動します。
2番目のALTER TABLEコマンドはオプションであり、mytableテーブルに関連するプロジェクションを移動します。RESTRICT REFERENCESオプションは、移動先のスキーマ内に存在するプロジェクションのみを移動することを指定します。デフォルトでは、全てのプロジェクションが移動されます。

このサンプルコードを参考に、状況に合わせてスキーマ名やテーブル名を変更してください。

補足

このコードは、PostgreSQL 9.1以降で使用できます。
テーブルを移動する前に、十分な権限を持っていることを確認してください。

PostgreSQLでテーブルを移動するその他の方法

pg_dumpとpg_restoreを使用する

pg_dumpコマンドを使用して、移動対象のテーブルを含むダンプファイルを作成します。

pg_dump -d source_database -t table_name > table_dump.sql

source_databaseは移動元データベース名に置き換えます。

pg_restoreコマンドを使用して、ダンプファイルを移動先データベースに復元します。

pg_restore -d target_database table_dump.sql

CREATE TABLE AS ... SELECT ...を使用する

以下のクエリを使用して、移動先のスキーマに新しいテーブルを作成します。

CREATE TABLE target_schema.table_name AS
SELECT * FROM source_schema.table_name;

オプションで、移動元テーブルをドロップします。

DROP TABLE source_schema.table_name;

それぞれの方法の利点と欠点

方法	利点	欠点
`ALTER TABLE`	簡単、スキーマ定義を維持	関連オブジェクトを手動で移動する必要がある
`pg_dump`と`pg_restore`	関連オブジェクトも含めて移動できる	時間とディスク容量がかかる
`CREATE TABLE AS ... SELECT ...`	シンプル	スキーマ定義を変更する必要がある場合、複雑になる