【PostgreSQL】ＳQL構文（DDL)：テーブルの作成/削除

ＳQL構文（DDL）：テーブルの作成/削除
1. テーブル
  1. テーブルの作成
    1. 基本構文
    2. オプション付きの構文
  2. テーブルの削除
    1. 基本構文／オプションの詳細

ＳQL構文（DDL）：テーブルの作成/削除

テーブル

テーブルの作成

基本構文

テーブルの作成には、CREATE TABLE を使用します。

CREATE TABLE table_name (
    column_name1 data_type [column_constraints],
    column_name2 data_type [column_constraints],
    ...
) [table_options];

data_type：必要に応じて適切なデータ型を指定します。
column_constraints：必要に応じてカラムの制約（NOT NULL, PRIMARY KEY, DEFAULTなど）を指定します。

以下にmy_tableの作成例を示します。

CREATE TABLE my_table (
    id_no INTEGER PRIMARY KEY,
    id_name TEXT
);

オプション付きの構文

以下のオプション付きの基本構文を示します。

CREATE TABLE table_name (
    column_name1 data_type [column_constraints],
    column_name2 data_type [column_constraints],
    ...
    [table_constraints]
) 
WITH (
    FILLFACTOR = value,
    autovacuum_enabled = boolean,
    toast.autovacuum_enabled = boolean,
    ...
)
TABLESPACE tablespace_name
PARTITION BY partition_method (partition_column)
[INHERITS (parent_table)]
[ON COMMIT {PRESERVE ROWS | DELETE ROWS | DROP}]
[WITHOUT OIDS];

●table_constraints：テーブルに適用される制約です。
　テーブル全体に関わるルールや制限（PRIMARY KEY（主キー）やUNIQUE（ユニーク制約）等です。

●FILLFACTOR：データページをどの程度まで使用するかを指定します。

デフォルト値： 100 。データページのスペースを全て使用します。
値の範囲：10～100。低い値を指定すると、空きスペースを多く残せます。

　使用ケース

頻繁に行のサイズが増減するようなテーブル
値を低めに設定します。ページの分割（ページスプリット）や新しいページへの移動を減らすことができます。
大量のデータが追加される可能性があるテーブル
値を低めに設定します。これもページ分割によるパフォーマンス低下を避けることができます。

CREATE TABLE my_table (
    id SERIAL PRIMARY KEY,
    data TEXT
) WITH (FILLFACTOR = 70);

●autovacuum_enabled：データベーステーブルの自動メンテナンス実行指定
　不要な行の削除やインデックスのリフレッシュを、自動で実行するか否かを指定します。
　デフォルトは trueです。

CREATE TABLE my_table (
    id SERIAL PRIMARY KEY,
    data TEXT
) WITH (FILLFACTOR = 70, autovacuum_enabled = false);

●toast.autovacuum_enabled：大きなデータを格納する特殊テーブル（toast）のメンテナンスを
　自動実行するかしないかの指定です。デフォルト設定は trueです。

CREATE TABLE my_table (
    id SERIAL PRIMARY KEY,
    data TEXT
) WITH (FILLFACTOR = 70, autovacuum_enabled = false, toast.autovacuum_enabled = false);

●TABLESPACE：データベースオブジェクト（テーブル、インデックス等）の物理的な格納場所を
　指定する機能です。
　デフォルトは（pg_default テーブルスペース）ですが、他のテーブルスペースに格納すると
　データベースのストレージを効率的に管理できます。

CREATE TABLE my_table (
    id SERIAL PRIMARY KEY,
    data TEXT
) TABLESPACE my_tablespace;

●PARTITION BY：管理を容易にするために、大きなテーブルを論理的に分割する機能です。
　以下の３種類の方法があります。

範囲パーティショニング（Range Partitioning）
日付や数値等の特定な値の範囲に基づいてテーブルを分割します。
リストパーティショニング（List Partitioning）
地域やカテゴリなどの固定された値に基づいて分割します。
ハッシュパーティショニング（Hash Partitioning）
ハッシュ関数を使用して、テーブルを均等に分割します。

CREATE TABLE sales (
    sale_id SERIAL PRIMARY KEY,
    sale_date DATE,
    amount NUMERIC
) PARTITION BY RANGE (sale_date);

●INHERITS：テーブル作成時、既存のテーブルを継承して作成します。
　作成されたテーブルは、親テーブルの構造を継承しますが、独自の構造も追加できます。

CREATE TABLE my_sub_table (
    extra_column TEXT
) INHERITS (parent_table);

●ON COMMIT：トランザクションのコミット時、一時テーブルをどのように扱うかを指定します。
　以下の3つのオプションがあります。

ON COMMIT DELETE ROWS
データを全て削除しますが、テーブルの構造を残します。
複数のトランザクションで同じテーブル構造を使用したい場合に指定します。
ON COMMIT DROP
一時テーブルのデータと構造を削除します。
ON COMMIT PRESERVE ROWS
一時テーブルのデータと構造をそのまま残します。
複数のトランザクションにまたがって一時テーブルを使用したい場合に指定します。

CREATE TEMPORARY TABLE temp_table (
    id SERIAL PRIMARY KEY,
    data TEXT
) ON COMMIT DROP;

●WITHOUT OIDS：PostgreSQL が使用するユニークな識別子（OIDS）を含めないようにします。
　ユーザーのアプリケーションレベルでは使用しない為、バージョン12以降はデフォルトとなって
　います。

例）複数のオプションを使用したテーブルの作成

CREATE TABLE my_table (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP
) 
WITH (FILLFACTOR = 70, autovacuum_enabled = true)
TABLESPACE my_tablespace
PARTITION BY RANGE (created_at);

このコマンドは以下のようにmy_tableを作成します。

●列定義