【PostgreSQL】 ＳQL構文（DDL)：テーブルの変更

2024.09.07

ＳQL構文（DDL)：テーブル変更

テーブル（TABLE）

テーブル名の変更


基本構文

ALTER TABLE old_table_name RENAME TO new_table_name;


テーブル名称「old_table_name」を新テーブル名称「new_table_name」に変更します。

  • 依存関係
    この「old_table_name」に依存している、他のテーブルやビュー、関数、トリガーなどは、自動的に変更されません。従って、「old_table_name」に依存しているオブジェクトは手動で変更する必要があります。



列の追加


基本構文

ALTER TABLE table_name ADD COLUMN new_column_name data_type [options];

●列が追加される場所
　常にテーブルの最後尾に追加します。列を特定の位置に追加する事はできません。


●オプション一覧

  • デフォルト値の設定 (DEFAULT)
    追加する列にデフォルト値を指定します。この場合、INSERT文で値が与えられなかった場合にこのデフォルト値が使われます。
ALTER TABLE table_name ADD COLUMN new_column_name data_type DEFAULT default_value;


  • NULLを許可しない制約 (NOT NULL)
    追加した列にNULL値を設定できないように制約します。
ALTER TABLE table_name ADD COLUMN new_column_name data_type DEFAULT NOT NULL;


  • 一意制約 (UNIQUE)
    重複した値が許可されないように、一意制約を設定します。
ALTER TABLE table_name ADD COLUMN new_column_name data_type UNIQUE;


  • チェック制約 (CHECK)
    列に条件を指定して、その条件を満たさないデータの設定できないようにします。
ALTER TABLE table_name ADD COLUMN new_column_name data_type CHECK (条件式);


  • 自動生成されるID列 (GENERATED)
    自動生成される数値型のID列を追加します。
ALTER TABLE table_name ADD COLUMN new_column_name BIGINT GENERATED ALWAYS AS IDENTITY;


列の削除


基本構文

ALTER TABLE table_name DROP drop_column_name [options];


●オプション一覧

  • CASCADE
    削除する列に依存しているオブジェクト（ビュー、トリガー、制約など）も一緒に削除されます。
ALTER TABLE table_name DROP drop_column_name CASCADE;


  • RESTRICT（デフォルト）
    削除する列に依存しているオブジェクトがある場合、列の削除を実施しません。
ALTER TABLE table_name DROP drop_column_name RESTRICT;


列の型変換


基本構文

ALTER TABLE table_name ALTER COLUMN column_name SET DATA TYPE new_data_type [USING 式];


「column_name」のデータ型を「new_data_type」に変換します。型変換を行う場合は、既存のデータが新しい型に適合するかを確認する必要があります。変換が失敗するとエラーが発生するため、変換ケースによってはUSING句を使用して適切な変換を指定します。


  • integerからtextに変換
    この場合、数値から文字列への変換でエラーは発生しないので、USING句は不要です。
ALTER TABLE table_name ALTER COLUMN column_name SET DATA TYPE text;


  • USING句を使った型変換(textをintegerに変換）
ALTER TABLE table_name ALTER COLUMN column_name SET DATA TYPE integer USING column_name::integer;


::integer はPostgreSQLのキャスト演算子で、この場合は「text」を「integer」に変換します。しかし、変換できない値が入っていた場合、エラーが発生する為、USING句を使用して例外処理や追加のキャストを行います。


  • NULLに変換する
    数値に変換できないデータを「NULL」に置き換えてエラーを発生させないようにします。
    下記の例では、正規表現~ '^\d+$'を使って、column_name列に数値だけが入っている場合はキャストし、それ以外の値はNULLに変換しています。
ALTER TABLE table_name
ALTER COLUMN column_name SET DATA TYPE integer USING (CASE WHEN column_name ~ '^\d+$' THEN column_name::integer ELSE NULL END);


  • デフォルト値を設定する
    無効なデータに対して、デフォルトの整数値を設定することも可能です。
    デフォルト値として、数値の「0」を設定し、エラーを発生させないようにしています。


ALTER TABLE table_name
ALTER COLUMN column_name SET DATA TYPE integer USING (CASE WHEN column_name ~ '^\d+$' THEN age::integer ELSE 0 END);


列のデフォルト値変更


基本構文

ALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT new_default_value;

「column_name」のデフォルト値を「new_default_value」に変換します。

  • デフォルト値を持つ列の追加
    列を新規に追加する際にデフォルト値を設定したい場合は、ADD COLUMNとDEFAULTを使用します。
ALTER TABLE table_name ADD COLUMN new_column_name data_type DEFAULT default_value;


列のデフォルト値削除


基本構文

ALTER TABLE table_name ALTER COLUMN column_name DROP DEFAULT;

  • 既存データへの影響
    デフォルト値の変更は新しいデータにのみ適用され、既存のデータに影響しません。既存データをデフォルト値に変更する場合は、「UPDATE」文を使用します。


インデックスの作成


基本構文

CREATE INDEX index_name ON table_name (column_name [ASC | DESC]);


  • index_name：インデックスの名前
  • table_name：インデックスを作成するテーブル名
  • column_name: インデックスを作成する列名
  • ASC または DESC: インデックスが昇順か降順かを指定します。デフォルトはASC（昇順）です。

パフォーマンスを最適化するには、クエリの内容やテーブルのデータ型に応じて適切なインデックスを作成することが重要です。


インデックス作成のオプション

  • ユニークインデックス（UNIQUE）
    重複する値が無い列をインデックスとして作成する場合に指定します。これにより一意の値を検索するクエリに対して、より効率的に結果を取得できます。
    下記は、「email」列にユニークインデックスを作成し、「email」列をより高速に検索しています。
CREATE UNIQUE INDEX idx_users_email ON users (email);
SELECT * FROM users WHERE email = 'example@example.com';


  • 複合インデックス
    複数の列を組み合わせてインデックス（複合インデックス）を作成する場合に指定します。これにより複数の列に基づくクエリのパフォーマンスが向上します。
CREATE INDEX index_name ON table_name (column1, column2);


  • パーティションインデックス
    パーティショニングされたテーブルでは、各パーティションごとにインデックスを作成できます。全体のデータをスキャンするのではなく、必要なパーティションだけを対象とするため、パフォーマンスが向上します。
CREATE INDEX index_name ON ONLY partition_name (column_name);


インデックスの削除


DROP INDEX [ CONCURRENTLY ] [ IF EXISTS ] index_name [ CASCADE | RESTRICT ];
  • CONCURRENTLY
    テーブルに対して書き込み操作が可能な状態を維持しながらインデックスを削除します。ただし、パフォーマンスが低下する可能性があります。
    注意: CONCURRENTLYオプションは、トランザクション内で使用できません。
  • IF EXISTS
    存在しないインデックスを削除しようとする際のエラーを防ぎます。
  • CASCADE | RESTRICT
    ・CASCADE：インデックスに依存しているオブジェクトも一緒に削除します。
    ・RESTRICT：インデックスに依存しているオブジェクトがある場合、エラーを返します。（デフォルト値）

タイトルとURLをコピーしました