🐘
PostgreSQLの列挙型(enum)に関するメモ
PostgreSQLの列挙型(enum)に関するメモです。
利用方法
宣言
CREATE TYPEで宣言します。
CREATE TYPE job_status AS ENUM ('running', 'finished');
データ型としての利用
作成した列挙型は、他のデータ型と同じように使えます。
CREATE TABLE jobs (
name text,
status job_status
);
INSERTなどで指定する際には、文字列として指定します。
INSERT INTO jobs VALUES ('job1', 'running');
SELECT * FROM jobs WHERE status = 'running';
これらは暗黙的なキャストが行われる形になっており、
test=# EXPLAIN SELECT * FROM jobs WHERE status = 'running';
QUERY PLAN
------------------------------------------------------
Seq Scan on jobs (cost=0.00..25.88 rows=6 width=36)
Filter: (status = 'running'::job_status)
(2 rows)
存在しないものを指定するとエラーになります。
test=# SELECT * FROM jobs WHERE status = 'xxxxx';
ERROR: invalid input value for enum job_status: "xxxxx"
LINE 1: SELECT * FROM jobs WHERE status = 'xxxxx';
^
削除
DROP TYPEで削除します。
DROP TYPE job_status;
当然のことながら、利用されていると消せません。
test=# DROP TYPE job_status;
ERROR: cannot drop type job_status because other objects depend on it
DETAIL: column status of table jobs depends on type job_status
HINT: Use DROP ... CASCADE to drop the dependent objects too.
列挙型の値の確認方法
列挙型で定義されている値はpg_typeとpg_enumにて確認できます。
SELECT
pg_namespace.nspname
, pg_type.typname
, pg_enum.enumsortorder
, pg_enum.enumlabel
FROM
pg_type
INNER JOIN pg_enum
ON (pg_type.oid = pg_enum.enumtypid)
INNER JOIN pg_namespace
ON (pg_type.typnamespace = pg_namespace.oid)
WHERE
pg_type.typtype = 'e'
ORDER BY
pg_namespace.nspname
, pg_type.typname
, pg_enum.enumsortorder
, pg_enum.enumlabel
;
test=# SELECT
test-# pg_namespace.nspname
test-# , pg_type.typname
test-# , pg_enum.enumsortorder
test-# , pg_enum.enumlabel
test-# FROM
test-# pg_type
test-# INNER JOIN pg_enum
test-# ON (pg_type.oid = pg_enum.enumtypid)
test-# INNER JOIN pg_namespace
test-# ON (pg_type.typnamespace = pg_namespace.oid)
test-# WHERE
test-# pg_type.typtype = 'e'
test-# ORDER BY
test-# pg_namespace.nspname
test-# , pg_type.typname
test-# , pg_enum.enumsortorder
test-# , pg_enum.enumlabel
test-# ;
nspname | typname | enumsortorder | enumlabel
---------+------------+---------------+-----------
public | job_status | 1 | running
public | job_status | 2 | finished
public | job_status | 3 | pending
(3 rows)
値の追加
ALTER TYPE ... ADD VALUE で追加します。
ALTER TYPE job_status ADD VALUE 'pending';
BEFORE or AFTER を使って、特定の位置に追加することもできます。
ALTER TYPE job_status ADD VALUE 'pending' BEFORE 'finished';
test=# SELECT
test-# pg_namespace.nspname
test-# , pg_type.typname
test-# , pg_enum.enumsortorder
test-# , pg_enum.enumlabel
test-# FROM
test-# pg_type
test-# INNER JOIN pg_enum
test-# ON (pg_type.oid = pg_enum.enumtypid)
test-# INNER JOIN pg_namespace
test-# ON (pg_type.typnamespace = pg_namespace.oid)
test-# WHERE
test-# pg_type.typtype = 'e'
test-# ORDER BY
test-# pg_namespace.nspname
test-# , pg_type.typname
test-# , pg_enum.enumsortorder
test-# , pg_enum.enumlabel
test-# ;
nspname | typname | enumsortorder | enumlabel
---------+------------+---------------+-----------
public | job_status | 1 | running
public | job_status | 2 | finished
(2 rows)
test=# ALTER TYPE job_status ADD VALUE 'pending' BEFORE 'finished';
ALTER TYPE
test=# SELECT
test-# pg_namespace.nspname
test-# , pg_type.typname
test-# , pg_enum.enumsortorder
test-# , pg_enum.enumlabel
test-# FROM
test-# pg_type
test-# INNER JOIN pg_enum
test-# ON (pg_type.oid = pg_enum.enumtypid)
test-# INNER JOIN pg_namespace
test-# ON (pg_type.typnamespace = pg_namespace.oid)
test-# WHERE
test-# pg_type.typtype = 'e'
test-# ORDER BY
test-# pg_namespace.nspname
test-# , pg_type.typname
test-# , pg_enum.enumsortorder
test-# , pg_enum.enumlabel
test-# ;
nspname | typname | enumsortorder | enumlabel
---------+------------+---------------+-----------
public | job_status | 1 | running
public | job_status | 1.5 | pending
public | job_status | 2 | finished
(3 rows)
ALTER TYPE ... ADD VALUE には制限があって、トランザクションブロック内で実行された場合、トランザクションがコミットされるまで新しい値を参照することはできません。
(PostgreSQL 11までは、トランザクションブロック内で実行自体出来なかったので、その時よりは制限が緩くなっています)
test=# start transaction;
START TRANSACTION
test=*# ALTER TYPE job_status ADD VALUE 'pending';
ALTER TYPE
test=*# INSERT INTO jobs VALUES ('job1', 'pending');
ERROR: unsafe use of new value "pending" of enum type job_status
LINE 1: INSERT INTO jobs VALUES ('job1', 'pending');
^
HINT: New enum values must be committed before they can be used.
トランザクションブロック内で値を追加して、その値を設定するということが出来ないので、もしもそのようにしたい場合には、列挙型自体を作り直すといった方法を取る必要があります。
列挙型を作り直す際の手順は後述します。
値の名前変更
ALTER TYPE ... RENAME VALUE で変更します。
ALTER TYPE job_status RENAME VALUE 'pending' TO 'suspended';
値の削除
値を削除する方法はありません。
こういった場合には、列挙型自体の作り直しが必要になります。
列挙型を作り直す際の手順は後述します。
列挙型の作り直し
下記のような場合に、列挙型を作り直す必要が出てきます。
-
ALTER TYPE ... ADD VALUEで追加した値を、同一トランザクションブロック内で参照したい場合(追加した値を設定するところまでを一つのトランザクションとしたいような場合) - 値を削除したい場合
こういった場合には、新しい列挙型を作成し、その列挙型にデータ型を変えるような手順を取ることになります。
- 古い列挙型の名前を変更
- 同じ名前で新しい列挙型を作成
- 古い列挙型を利用していたテーブルのカラムのデータ型を新しい列挙型に変更
- 古い列挙型を削除
例えば、下記のように running pending finished と3つの値がある列挙値が既にテーブルで利用されている状態で、
CREATE TYPE job_status AS ENUM ('running', 'pending', 'finished');
CREATE TABLE jobs (
name text,
status job_status
);
INSERT INTO jobs VALUES ('job1', 'running');
INSERT INTO jobs VALUES ('job2', 'finished');
pending を削除したい場合を例として手順を記載します。
古い列挙型の名前を変更します。
ALTER TYPE job_status RENAME TO job_status_old;
同じ名前で新しい列挙型を作成します。(pendingを抜いたもの)
CREATE TYPE job_status AS ENUM ('running', 'finished');
古い列挙型を利用していたテーブルのカラムのデータ型を新しい列挙型に変更します。
この際にUSING使うことで、既に存在するレコードの値を新しい列挙型へ変換します。(いったんtextにしたうえで列挙型にキャスト)
ALTER TABLE jobs
ALTER COLUMN status TYPE job_status USING status::text::job_status;
USINGではCASE文もかけるので、もう少し複雑な変換もできます。
たとえば今回は削除対象のpendingが設定されていたレコードがありませんでしたが、pendingがあった場合にNULLにする場合には下記のように書きます。
ALTER TABLE jobs
ALTER COLUMN status TYPE job_status USING
CASE WHEN status = 'pending'
THEN NULL
ELSE status::text::job_status
END;
古い列挙型を削除します。
DROP TYPE job_status_old;
Discussion