ENUM type in PostgreSQL
We create:
CREATE TYPE colors AS ENUM ('grey', 'skyblue', 'black');
Let’s look at the elements:
SELECT ENUM_RANGE(null::colors);
or
SELECT UNNEST(ENUM_RANGE(null::colors)) AS colors;
Adding elements:
ALTER TYPE colors ADD VALUE 'orange' AFTER 'skyblue';
Removing an element:
ALTER TYPE colors DROP attribute 'orange';
And remove the enum type:
DROP type colors;
There is a more elegant way using a domain to provide value. I only used it once.
CREATE DOMAIN eye_color AS TEXT CONSTRAINT valid_eye_colors CHECK (
VALUE IN ( 'blue', 'green', 'brown' )
);
CREATE TABLE faces (
face_id SERIAL PRIMARY KEY,
name TEXT NOT NULL DEFAULT 'Anonymous',
eye_color eye_color NOT NULL
);