CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name (
{ column_name data_type [ DEFAULT default_expr ] [ column_constraint [, ... ] ]
| table_constraint
| LIKE parent_table [ { INCLUDING | EXCLUDING } DEFAULTS ] } [, ... ]
)
[ INHERITS ( parent_table [, ... ] ) ]
[ WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
ここで、column_constraint は以下のとおりです。
[ CONSTRAINT constraint_name ]
{ NOT NULL | NULL | UNIQUE | PRIMARY KEY |
CHECK (expression) |
REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
[ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
また、table_constraint は以下のとおりです。
[ CONSTRAINT constraint_name ]
{ UNIQUE ( column_name [, ... ] ) |
PRIMARY KEY ( column_name [, ... ] ) |
CHECK ( expression ) |
FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
[ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]CREATE TABLE は新しい、空のテーブルを現在のデータベースに作成します。 テーブルはこのコマンドを実行したユーザによって所有されます。
スキーマ名が付けられている場合 (たとえば、CREATE TABLE myschema.mytable ...)、テーブルは指定されたスキーマで作成されます。 スキーマ名がなければ、テーブルは現在のスキーマで作成されます。 また、一時テーブルは特別なスキーマに存在するため、一時テーブルの作成時にスキーマ名を与えることはできません。 テーブル名は、同じスキーマ内の他のテーブル、シーケンス、インデックス、もしくはビューとは異なる名前にする必要があります。
CREATE TABLE はまた自動的に、そのテーブルの 1 行に対応する複合型を表す、データ型を作成します。 したがって、テーブルは、同じスキーマ内の既存のデータ型と同じ名前を持つことができません。
テーブルは 1600 列以上の列を持つことはできません。 (実際は、タプル長の制限により実際の制限はもっと低くなります。)
省略可能な制約句は、新しい、または、更新された行がその挿入、更新操作を成功させるために満たさなければならない制約(または試験項目)を指定します。 制約は、テーブル内で有効な値集合の定義を補助する SQL オブジェクトです。
制約の定義にはテーブル制約と列制約という 2 つの方法があります。 列制約は列定義の一部として定義されます。 テーブル制約定義は特定の列には拘束されず、複数の列を含有することができます。 また各列制約は、テーブル制約として記述することができます。 列制約は、その制約が 1 つの列にのみ影響する場合の、簡便な記述方法にすぎません。
指定された場合、テーブルは一時テーブルとして作成されます。 一時テーブルは、そのセッションの終り、場合によっては、現在のトランザクションの終り(後述のON COMMITを参照)に自動的に削除されます。 一時テーブルが存在する場合、同じ名前を持つ既存の永続テーブルはスキーマ修飾された名前で参照されていなければ、現在のセッションでは非可視です。 一時テーブルで作られるインデックスも全て自動的に一時的のものになります。
GLOBAL または LOCALをTEMPORARY や TEMP の後に記述することができます(省略可能)。 PostgreSQLでは、これらに違いはありません。 互換性を参照してください。
作成するテーブルの名前です (スキーマ修飾名でも可)。
新しいテーブルで作成される列の名前です。
列のデータ型です。これには、配列指定子が含まれる場合があります。
DEFAULT 句はそれを記載した列定義の列にデフォルトデータ値を割り当てます。 その値は任意の無変数式です (副selectと現在のテーブル内の他の列へ交差参照は許可されません)。 デフォルト式のデータ型はその列のデータ型と一致する必要があります。
デフォルト式は、その列に値が指定されない、全ての挿入操作において使用されます。 列にデフォルトがない場合、デフォルト値は NULL になります。
LIKE 句は、新しいテーブルが自動的に全ての列名、そのデータ型、および、非NULL制約を継承するテーブルを指定します。
INHERITS との違いは、作成した後、新しいテーブルと継承されたテーブルとの完全に分離されることです。 新しいテーブルに挿入されたデータは、親テーブルには反映されません。
継承される列用のデフォルト式は、INCLUDING DEFAULTS が指定された場合にのみ含まれます。 デフォルトでは、デフォルト式は含まれません。
省略可能な INHERITS 句は、新しいテーブルがその列を全て継承するテーブルの一覧を指定します。 複数の親テーブルに同一名の列が存在する場合、そのデータ型も一致していなければ、エラーとして報告されます。 競合がなければ、重複した列は新しいテーブルで 1 つの列の形に融合されます。 新しいテーブルの列名の一覧に継承された列が含まれる場合、同様にそのデータ型は継承した列のデータ型に一致しなければならず、その列定義は 1つに融合されます。 しかし、継承された新しい、同一名称の列定義に、同一の制約を指定する必要はありません。 任意の宣言で提供される制約はすべて、まとめてマージされ、全てが新しいテーブルに適用されます。 新しいテーブルが明示的にその列にデフォルト値を指定していた場合、その列の継承された宣言における全てのデフォルト値は上書きされます。 さもなくば、その列にデフォルト値を指定した親テーブルは全て、同じデフォルト値を持たなければなりません。 この他の場合はエラーになります。
この省略可能な句は、新しいテーブルの行に OID(オブジェクト識別子) を割り当てるかどうかを指定します。 デフォルトでは OID を持ちます。(OID を持つテーブルを継承する新しいテーブルでは、WITHOUT OIDS を指定したとしても強制的にWITH OIDS となります。)
WITHOUT OIDS を指定することにより、ユーザはテーブルの行のための OID 生成を抑制することができます。 これは、OID の消費を抑え、32 ビットの OID カウンタの回転周期を延長することができますので、巨大なテーブルでは価値がある可能性があります。 カウンタが一周すると、OID の一意性を仮定することができなくなり、その有用性を減少させることになります。 また、WITHOUT OIDSを指定することで、テーブル1行当たり4バイト分そのテーブルをディスクに格納するための容量を軽減され、その分、性能が向上します。
省略可能な列制約、テーブル制約の名前です。 指定されなければ、システムが名前を生成します。
列は NULL 値を持つことができません。
列は NULL 値を持つことができます。これがデフォルトです。
この句は非標準的な SQL データベースとの互換性のためだけに使用可能です。 新しいアプリケーションでこれを使用しないで下さい。
UNIQUE 制約は、テーブルの1つ以上の別々な列のグループは一意な値のみを持つことができることを指定しています。 一意性テーブル制約の動作は列制約と同様ですが、更に複数列に跨る機能を持ちます。
一意性制約の目的のために、NULL 値は等しいとはみなされなせん。
各一意性テーブル制約は、そのテーブルで定義された他の一意性制約もしくはプライマリキー制約によって名付けられた列の集合とは異なるように列の集合を名付けなければなりません。 (さもなくば、同じ制約が2回あらわれるだけになります。)
プライマリキー制約は、テーブルの1列または複数列が一意な(重複が無い)、非 NULL 値のみを持つことを指定します。 技術的には、PRIMARY KEY は単なるUNIQUE と NOT NULL の組み合わせですが、プライマリキーは、他のテーブルが一意な行の識別子としてこの列集合に依存することができることを意味しますので、プライマリキーとして列の集合を識別することは、スキーマ設計に関するメタデータも提供します。
1つのテーブルには列制約もしくはテーブル制約として 1 つのプライマリキーのみを指定することができます。
プライマリキー制約は、そのテーブルに定義された他全ての一意性制約で名付けられた列の集合とは異なるように、列の集合に名前を付けなければなりません。
CHECK 句は、新しい、または、変更された行が挿入や更新操作が成功するために満たさなければならない、Boolean型の結果を生成する式を指定します。 列定義内の制約は、その列の値のみを参照しなければなりません。一方、テーブル制約として現れる条件は、複数列を参照することができます。
現在、CHECK 式には、副selectや現在の行内の列以外の値を含むことはできません。
これらの句は、外部キー制約を指定します。 これは、新しいテーブルの 1 つ以上の列のグループが被参照テーブル reftable の被参照列(複数可) refcolumn 内の値に一致する値のみを持たなければならないことを指定するものです。 refcolumn が省略された場合、reftable のプライマリキーが使用されます。 被参照列は被参照テーブルにおいて、一意性もしくはプライマリキー制約をもった列でなければなりません。
これらの列に追加された値は、被参照テーブルと被参照列の値に指定された照合型で照会されます。 3種類の照合型があります。MATCH FULL、MATCH PARTIAL、デフォルトでもあるMATCH SIMPLE照合型です。 MATCH FULL は全ての外部キー列が NULL となる場合を除き、複数列外部キーのある列がNULLとなることを許可しません。 MATCH SIMPLE 照合型は、外部キーの他の部分が NULL でない限り、外部キーの一部を NULL となることを許可します。 MATCH PARTIAL はまだ実装されていません。
更に、被参照列のデータが変更された場合、このテーブルの列のデータに何らかの動作が行なわれます。 ON DELETE 句は被参照テーブルの被参照行が削除された場合の動作を指定します。 同様に ON UPDATE 句は被参照テーブルの被参照列が新しい値に更新された場合に行なわれる動作を指定します。 被参照列に実際に変更がないように行が更新された場合は、動作は行なわれません。 各句について、以下の動作を指定可能です。
削除もしくは更新が外部キー制約違反となることを示すエラーを発生します。これはデフォルトの動作です。
NO ACTION と同じです。 ただし、制約の残りが遅延可能かつ遅延状態であったとしても、この動作は遅延されません。
削除された行を参照する行を全て削除、もしくは、被参照列の新しい値に、参照する列の値を更新します。
参照する列の値を NULL に設定します。
参照する列の値をそのデフォルト値に設定します。
プライマリキー列が頻繁に更新される場合、外部キー列にインデックスを付け、外部キー列に関連した NO ACTION や CASCADE 動作がより効率的に実行できるようにする方がいいでしょう。
これは制約を延期させることが可能かどうかを制御します。 延期させられない制約は各コマンドの後すぐに検査されます。 延期可能な制約の検査は (SET CONSTRAINTS コマンドを使用して)トランザクションの終了時まで延期することができます。 NOT DEFERRABLE がデフォルトです。 現在、外部キー制約のみがこの句を受け付けることができます。他の種類の全ての制約は延期させられません。
制約が延期可能な場合、この句は制約検査を行なうデフォルトの時期を指定します。 制約が INITIALLY IMMEDIATE の場合、各文の実行後に検査されます。 これがデフォルトです。 制約がINITIALLY DEFERRED の場合、トランザクションの終了時にのみ検査されます。 制約検査の時期は SET CONSTRAINTS コマンドを使用して変更することができます。
ON COMMIT を使用して、トランザクションブロックの終了時点での一時テーブルの動作を制御することができます。 以下の3つのオプションがあります。
アプリケーションがテーブルの特定の行を識別するために OID の使用を行なう場合は、そのテーブルの oid 列に UNIQUE 制約を作成することを推奨します。 これによりカウンタが一周してしまった場合でも一意に行を識別できることが保証されるからです。 データベース全体の一意な識別子が必要となる場合、OID がテーブル全体で一意であると仮定することは止めて下さい。 この場合は tableoid と 行の OID の組み合わせを使用して下さい。 (今後の PostgreSQL のリリースでは、各テーブル毎に別々の OID カウンタを使用する予定です。 つまり、データベース全体で一意な識別子を得るために tableoid を使用することは、省略できることではなく、必要なことになります。)
ティップ: WITHOUT OIDS の使用は、プライマリキーの無いテーブルでは推奨できません。 OID も一意なデータキーも存在しませんので、特定行の識別を行なうことが難しくなるからです。
PostgreSQL は自動的に各一意性制約とプライマリキー制約に対してインデックスを作成し、その一意性を確実なものにします。 従って、プライマリキーの列に明示的なインデックスを作成することは必要ありません。 (より詳細については CREATE INDEX を参照して下さい。)
現在の実装では、一意性制約とプライマリキーは継承されません。 これは継承と一意性制約の組み合わせはその機能に障害が起こるからです。
films テーブルと distributors テーブルを作成します。
CREATE TABLE films (
code char(5) CONSTRAINT firstkey PRIMARY KEY,
title varchar(40) NOT NULL,
did integer NOT NULL,
date_prod date,
kind varchar(10),
len interval hour to minute
);
CREATE TABLE distributors (
did integer PRIMARY KEY DEFAULT nextval('serial'),
name varchar(40) NOT NULL CHECK (name <> '')
);
2次元配列をもつテーブルを作成します。
CREATE TABLE array (
vector int[][]
);
films テーブルに 一意性テーブル制約を定義します。 一意性テーブル制約はテーブルの 1 つ以上の列に定義することができます。
CREATE TABLE films (
code char(5),
title varchar(40),
did integer,
date_prod date,
kind varchar(10),
len interval hour to minute,
CONSTRAINT production UNIQUE(date_prod)
);
CHECK 列制約を定義します。
CREATE TABLE distributors (
did integer CHECK (did > 100),
name varchar(40)
);
CHECK テーブル制約を定義します。
CREATE TABLE distributors (
did integer,
name varchar(40)
CONSTRAINT con1 CHECK (did > 100 AND name <> '')
);
films テーブルにプライマリキーテーブル制約を定義します。 プライマリキーテーブル制約はテーブルの 1 つ以上の列に定義することができます。
CREATE TABLE films (
code char(5),
title varchar(40),
did integer,
date_prod date,
kind varchar(10),
len interval hour to minute,
CONSTRAINT code_title PRIMARY KEY(code,title)
);
distributors テーブルにプライマリキー制約を定義します。 以下の 2 つの例は同じものです。 前者はテーブル制約構文を使用し、後者は列制約記法を使用します。
CREATE TABLE distributors (
did integer,
name varchar(40),
PRIMARY KEY(did)
);
CREATE TABLE distributors (
did integer PRIMARY KEY,
name varchar(40)
);
以下は、name 列のデフォルト値にリテラル定数を割り当て、did 列のデフォルト値をシーケンスオブジェクトの次の値を選択して生成されるように調整しています。 そして modtime のデフォルト値は、その行が挿入された時刻となります。
CREATE TABLE distributors (
name varchar(40) DEFAULT 'Luso Films',
did integer DEFAULT nextval('distributors_serial'),
modtime timestamp DEFAULT current_timestamp
);
2 つの NOT NULL 列制約を distributors テーブルに定義します。そのうち 1 つは明示的な名前を付けています。
CREATE TABLE distributors (
did integer CONSTRAINT no_null NOT NULL,
name varchar(40) NOT NULL
);
name 列に対し、一意性制約を定義します。
CREATE TABLE distributors (
did integer,
name varchar(40) UNIQUE
);上のコマンドはテーブル制約として指定した以下のコマンドと等価です。
CREATE TABLE distributors (
did integer,
name varchar(40),
UNIQUE(name)
);
CREATE TABLE は、以下の一覧を除いて、SQL92 に準拠し、SQL99のサブセットとなります。
CREATE TEMPORARY TABLE は標準SQLに類似していますが、その効果は同じではありません。 標準では、一時テーブルは一度のみ定義され、それを必要とするセッション毎に自動的に(空の内容で始まる形で)存在します。 PostgreSQL では、これと異なり、各セッションで独自に、使用する一時テーブル用の CREATE TEMPORARY TABLEコマンドを発行しなければなりません。 これにより、異なるセッションで同じ名前の一時テーブルを異なる目的で使用することができます。 一方、標準の方法では指定された一時テーブルの名前のインスタンスが全て同一のテーブル構造を持つという束縛があります。
標準における一時テーブルのこの動作定義は大抵無視されています。 この点でのPostgreSQLの動作は、他の多くのSQLデータベースと似ています。
標準における、グローバル一時テーブルとローカル一時テーブルの区別はPostgreSQLにはありません。 この区別はモジュール概念に依存したもので、PostgreSQLにはモジュール概念がないからです。 互換性という目的のため、PostgreSQL は一時テーブルの宣言においてGLOBAL と LOCAL キーワードを受け付けますが、これらは効果がありません。
一時テーブル用のON COMMIT句もまた、標準SQLに類似していますが、数点の違いがあります。 ON COMMIT句が省略された場合、SQLでは、デフォルトの動作はON COMMIT DELETE ROWSであると規定しています。 しかし、PostgreSQLでのデフォルトの動作はON COMMIT PRESERVE ROWSです。 ON COMMIT DROPはSQLにはありません。
標準SQLでは、CHECK 列制約はそれを適用する列のみを参照でき、CHECK テーブル制約のみが複数の列を参照できると言及しています。 PostgreSQL ではこの制限を強制していません。 列検査制約とテーブル検査制約を同様のものとして扱っています。
NULL "制約" (実際は非制約です)は標準 SQL に対する PostgreSQL 拡張で、他のいくつかのデータベースシステムとの互換性のために含まれています(そして NOT NULL 制約と対称になっています)。 どの列でもこれがデフォルトですので、この存在は邪魔なだけです。