SELECTコマンドで取得したカラムとデータを持つ新しいテーブルを作成する(CREATE TABLE AS)

CREATE TABLE AS コマンドを使うと他のテーブルなどから SELECT コマンドで取得したカラムとデータを持つ新しいテーブルを作成することができます。ここでは PostgreSQL で他のテーブルから取得したカラムとデータを使って新しいテーブルを作成する方法について解説します。

(Last modified: )

カラムとデータをコピーする

CREATE TABLE AS コマンドを使用すると、他のテーブルで定義されているカラムの中で必要なカラムだけを、データといっしょにコピーして新しいテーブルを作成することができます。書式は次の通りです。 

CREATE TABLE table_name
    AS query

query にはコピー元のテーブルの必要となるカラムを SELECT コマンドを使って記述します。また同時に取得するデータについても WHERE 句を使って条件設定をした上で取得することができます。

例えば次のように使用します。

(例)コピー元で定義されている id カラムと name カラムを持つテーブルを作成する。コピー元に格納されているデータの中で id カラムの値が 20 より大きいデータだけを新しく作成したテーブルに格納する。

CREATE TABLE table_name
  AS SELECT id, name FROM source_tbl WHERE id > 20

コピーされるカラムについて、コピーされうのはカラム名とデータ型だけで制約などはコピーされません。またコピー元で作成されたインデックスもコピーされません。またコピー元とコピー先のテーブルは完全に別のテーブルとして扱われますので、テーブル作成後にコピー元のテーブルに対して変更を行ったりデータの追加などを行ってもコピー先の新しいテーブルには一切反映されません。

-- --

それでは実際に試してみます。 mydb データベースの myschema スキーマにコピー元となる次のようなテーブルを作成しました。 

create table myschema.customer (
  id integer primary key,
  name varchar(10), 
  address varchar(10) default 'NoName'
);

カラムとデータをコピーする(1)

作成したテーブルには次のようなデータを追加しておきました。 

insert into myschema.customer values
  (1, 'Yamada', 'Tokyo'), 
  (3, 'Honda', 'Chiba'), 
  (4, 'Watanabe', 'Tokyo'),
  (7, 'Asada', 'Kanagawa'),
  (8, 'Hori', 'Tokyo');

カラムとデータをコピーする(2)

次に先ほど作成した customer テーブルから id カラムと name カラムをコピーし、データとして address カラムの値が 'Tokyo' と等しいデータだけをコピーしたテーブルを作成します。次のように実行してください。 

create table myschema.tokyocustomer as 
  select id, name from myschema.customer where address = 'Tokyo';

カラムとデータをコピーする(3)

新しいテーブルが作成され、データもコピー元から条件に一致したデータが格納されました。

psql メタコマンドの \d コマンドを使って作成したテーブルのカラムに関する情報を表示してみます。

\d myschema.tokyocustomer

カラムとデータをコピーする(4)

新しく作成されたテーブルでは指定したカラムがコピーされていますが、コピーされているのはカラム名とデータ型のみで、コピー元でカラムに指定されていた PRIMARY KEY 制約や DEFAULT 制約はコピーされませんでした。

次に新しく作成されたテーブルに格納されているデータを取得してみます。

select * from myschema.tokyocustomer;

カラムとデータをコピーする(5)

コピー元に格納されていてデータの中から、 WHERE 句で指定した条件に一致したデータだけが新しいテーブルに格納されていることが確認できました。

コピーするカラム名を任意の名前に変更する

特に指定しなければ新しく作成されるテーブルのカラムの名前はコピー元のカラム名が使用されますが、カラム名を指定してコピーすることができます。書式は次のとおりです。 

CREATE TABLE table_name (column_name [, ...] )
    AS query

SELECT コマンドで取得するカラムと同じ数だけ新しいカラム名( column_name )を指定してください。

それでは実際に試してみます。先ほど使用したコピー元のテーブルをそのまま使い、新しく作成されるテーブルのカラム名を指定してコマンドを実行してみます。 

create table myschema.tokyocustomer (id, username) as 
  select id, name from myschema.customer where address = 'Tokyo';

コピーするカラム名を任意の名前に変更する(1)

新しいテーブルが作成されました。新しいテーブルで定義されているカラムの名前は、コピー元のテーブルで定義されていたものではなく新しいテーブルを作成した時に指定した名前になっています。

psql メタコマンドの \d コマンドを使って作成したテーブルのカラムに関する情報を表示してみます。

\d myschema.tokyocustomer

コピーするカラム名を任意の名前に変更する(2)

新しく作成されたテーブルのカラム名が指定した名前になっていることが確認できました。

カラムだけをコピーしてデータはコピーしない

デフォルトの設定では CREATE TABLE AS コマンドを実行すると、カラムの情報に加えてデータもコピーされます。データはコピーせずカラムの情報だけをコピーする場合には次の書式を使います。 

CREATE TABLE table_name (column_name [, ...] )
    AS query WITH NO DATA

SELECT コマンドで指定したカラムの名前とデータ型コピーしたテーブルを作成しますが、データは一切コピーしません。

それでは実際に試してみます。先ほど使用したコピー元のテーブルをそのまま使い、次のようにコマンドを実行してみます。 

create table myschema.tokyocustomer as 
  select id, name from myschema.customer with no data;

カラムだけをコピーしてデータはコピーしない(1)

新しく作成したテーブルからデータを取得してみます。 

select * from myschema.tokyocustomer;

カラムだけをコピーしてデータはコピーしない(2)

テーブルにはデータが入っていないことが確認できました。

-- --

他のテーブルから取得したカラムとデータを使って新しいテーブルを作成する方法について解説しました。

( Written by Tatsuo Ikura )

プロフィール画像

著者 / TATSUO IKURA

これから IT 関連の知識を学ばれる方を対象に、色々な言語でのプログラミング方法や関連する技術、開発環境構築などに関する解説サイトを運営しています。