By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
-- create enum
create type gnum as enum ('test1', 'test2', 'test3');
CREATE TYPE
-- list values of enum
SELECT e.enumlabel
FROM pg_enum e
JOIN pg_type t ON e.enumtypid = t.oid
WHERE t.typname = 'gnum';
| enumlabel |
|---|
| test1 |
| test2 |
| test3 |
SELECT 3
-- add value
ALTER TYPE gnum ADD VALUE 'test4'; -- end
ALTER TYPE gnum ADD VALUE 'test11' BEFORE 'test2'; -- in between
ALTER TYPE gnum ADD VALUE 'test12' AFTER 'test11'; -- in between
ALTER TYPE
ALTER TYPE
ALTER TYPE
-- list values of enum
SELECT e.enumlabel
FROM pg_enum e
JOIN pg_type t ON e.enumtypid = t.oid
WHERE t.typname = 'gnum';
| enumlabel |
|---|
| test1 |
| test2 |
| test3 |
| test4 |
| test11 |
| test12 |
SELECT 6
-- list values of enum in the order
SELECT t.typname, e.enumlabel, e.enumsortorder
FROM pg_type t, pg_enum e
WHERE t.oid = e.enumtypid ORDER BY e.enumsortorder;
| typname | enumlabel | enumsortorder |
|---|---|---|
| gnum | test1 | 1 |
| gnum | test11 | 1.5 |
| gnum | test12 | 1.75 |
| gnum | test2 | 2 |
| gnum | test3 | 3 |
| gnum | test4 | 4 |
SELECT 6
-- modify value
ALTER TYPE gnum
RENAME VALUE 'test1' TO 'test1-modified';
ALTER TYPE
-- list values
SELECT e.enumlabel
FROM pg_enum e
JOIN pg_type t ON e.enumtypid = t.oid
WHERE t.typname = 'gnum';
| enumlabel |
|---|
| test2 |
| test3 |
| test4 |
| test11 |
| test12 |
| test1-modified |
SELECT 6
-- enum values as ordered array
SELECT enum_range(NULL::gnum);
| enum_range |
|---|
| {test1-modified,test11,test12,test2,test3,test4} |
SELECT 1
-- enum values as ordered array, unnested
SELECT unnest(enum_range(NULL::gnum));
| unnest |
|---|
| test1-modified |
| test11 |
| test12 |
| test2 |
| test3 |
| test4 |
SELECT 6
DROP type gnum;
DROP TYPE
SELECT e.enumlabel
FROM pg_enum e
JOIN pg_type t ON e.enumtypid = t.oid
WHERE t.typname = 'gnum';
| enumlabel |
|---|
SELECT 0