Postgres 12

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

typname	enumlabel	enumsortorder
gnum	test1	1
gnum	test11	1.5
gnum	test12	1.75
gnum	test2	2
gnum	test3	3
gnum	test4	4