ENUM型から値を削除したい？PostgreSQLでできること

2024-04-02

PostgreSQLのENUM型値を削除する方法

もし、ENUM型から特定の値を削除したい場合は、以下の2つの方法があります。

列挙型を作り直す

新しいENUM型を作成し、削除したい値を含めないようにします。
既存の列挙型を使用しているテーブルを更新し、新しいENUM型を使用するように変更します。
既存のENUM型を削除します。

値を置き換える

削除したい値を持つデータを、新しい値に置き換えます。

例えば、colorという名前のENUM型があり、red、green、blueという値を持つ場合、redを削除したい場合は、以下のいずれかの方法を使用できます。

-- 新しいENUM型を作成
CREATE TYPE new_color AS ENUM ('green', 'blue');

-- テーブルを更新
ALTER TABLE my_table ALTER COLUMN color TYPE new_color USING color::new_color;

-- 古いENUM型を削除
DROP TYPE color;

UPDATE my_table SET color = 'green' WHERE color = 'red';

DROP TYPE color;

注意事項

列挙型を作り直す方法は、テーブル構造が変更されるため、影響を受けるアプリケーションがある場合は注意が必要です。
値を置き換える方法は、データの整合性が失われる可能性があるため、慎重に実行する必要があります。

-- テーブル作成
CREATE TABLE my_table (
  id INT PRIMARY KEY,
  color ENUM ('red', 'green', 'blue')
);

-- データ挿入
INSERT INTO my_table (id, color) VALUES (1, 'red'), (2, 'green'), (3, 'blue');

-- 新しいENUM型を作成
CREATE TYPE new_color AS ENUM ('green', 'blue');

-- テーブルを更新
ALTER TABLE my_table ALTER COLUMN color TYPE new_color USING color::new_color;

-- データ確認
SELECT * FROM my_table;

-- 古いENUM型を削除
DROP TYPE color;

-- テーブル作成
CREATE TABLE my_table (
  id INT PRIMARY KEY,
  color ENUM ('red', 'green', 'blue')
);

-- データ挿入
INSERT INTO my_table (id, color) VALUES (1, 'red'), (2, 'green'), (3, 'blue');

-- 値を置き換える
UPDATE my_table SET color = 'green' WHERE color = 'red';

-- データ確認
SELECT * FROM my_table;

-- 古いENUM型を削除
DROP TYPE color;

実行結果

-- 列挙型を作り直す

| id | color |
|---|---|
| 1 | green |
| 2 | green |
| 3 | blue |

-- 値を置き換える

| id | color |
|---|---|
| 1 | green |
| 2 | green |
| 3 | blue |

補足

上記のサンプルコードでは、my_tableというテーブルを作成し、idとcolorという2つの列を持つようにしています。color列は、red、green、blueという3つの値を持つENUM型です。

この方法では、まずnew_colorという名前の新しいENUM型を作成します。new_color型は、red値を含まないようにします。

次に、ALTER TABLEコマンドを使用して、my_tableテーブルのcolor列をnew_color型に変更します。USING句を使用して、既存のデータを変換します。

最後に、DROP TYPEコマンドを使用して、古いcolor型を削除します。

この方法では、まずUPDATEコマンドを使用して、color列がredであるデータをgreenに置き換えます。

PostgreSQLのENUM型値を削除するその他の方法

ビューを使用して、ENUM型から特定の値を除外した新しい仮想テーブルを作成することができます。

例

CREATE VIEW my_table_view AS
SELECT * FROM my_table
WHERE color != 'red';

SELECT * FROM my_table_view;

外部キーを使用して、別のテーブルのENUM型値を参照することができます。

-- テーブル作成
CREATE TABLE colors (
  id INT PRIMARY KEY,
  name VARCHAR(255)
);

CREATE TABLE my_table (
  id INT PRIMARY KEY,
  color_id INT REFERENCES colors (id)
);

-- データ挿入
INSERT INTO colors (id, name) VALUES (1, 'red'), (2, 'green'), (3, 'blue');

INSERT INTO my_table (id, color_id) VALUES (1, 1), (2, 2), (3, 3);

-- 外部キーを使用して値を削除
DELETE FROM colors WHERE id = 1;

-- my_tableテーブルから関連するデータも削除される
SELECT * FROM my_table;

トリガーを使用して、ENUM型値が変更される前に特定の処理を実行することができます。

CREATE TRIGGER my_trigger BEFORE UPDATE ON my_table
FOR EACH ROW
BEGIN
  IF NEW.color = 'red' THEN
    RAISE EXCEPTION 'red is not allowed';
  END IF;
END;

-- 'red'値を更新しようとするとエラーが発生
UPDATE my_table SET color = 'red' WHERE id = 1;