Data types that comprise a static, ordered set of values.
CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
The order in which the values were listed when the type was created. This will work in order by queries.
- An enum value occupies four bytes on disk.
- Case sensitive.
- Translations from internal enum values to textual labels are kept in the system catalog
pg_enum.
- Can't delete values from enum.source
- Can add new values in enum.
ALTER TYPE name ADD VALUE [ IF NOT EXISTS ] new_enum_value [ { BEFORE | AFTER } existing_enum_value ]
Note: Comparisons involving an added enum value will sometimes be slower than comparisons involving only original members of the enum type. This will usually only occur if BEFORE or AFTER is used to set the new value's sort position somewhere other than at the end of the list. However, sometimes it will happen even though the new value is added at the end (this occurs if the OID counter "wrapped around" since the original creation of the enum type). The slowdown is usually insignificant; but if it matters, optimal performance can be regained by dropping and recreating the enum type, or by dumping and reloading the database. source
- Can't reorder values in enums. Not able to find concrete source for this.
If deletion or reordering is required, create a new enum column, copy data and delete the old enum column.