CODEWITHSUNDEEP
databasepostgresqlenums
Alex
Alex

Reputation: 631

PostgreSQL: how to define ENUM type with custom emunsortorder values

Is there is a way to create ENUM type with custom order index. It is possible to update pg_type afterwards and set enumsortorder field to desired value, but the question is it possible to do during creation? E.g. something like 

CREATE TYPE environment AS ENUM ('stage' 10, 'prod' 20);

Upvotes: 0

Views: 507

Answers (1)

klin
klin

Reputation: 121754

The sort order is defined by the type declaration:

create type my_enum as enum ('first', 'third');

with t(v) as (
    values ('first'::my_enum), ('third')
)
select *
from t
order by 1;

   v   
-------
 first
 third
(2 rows)

You can add a new label in a desired position:

alter type my_enum add value 'second' before 'third';

with t(v) as (
    values ('first'::my_enum), ('third'), ('second')
)
select *
from t
order by 1;

   v    
--------
 first
 second
 third
(3 rows)

Read about CREATE TYPE and ALTER TYPE

If the above is not sufficient use a map function, e.g.:

create or replace function my_enum_map(my_enum)
returns int language sql as $$
    select case $1 
        when 'first' then 100
        when 'second' then 50
        when 'third' then 75
    end
$$;

with t(v) as (
    values ('first'::my_enum), ('third'), ('second')
)
select *
from t
order by my_enum_map(v);

   v    
--------
 second
 third
 first
(3 rows)

You can also store the values used by the function in a table to make modifications easier.

Upvotes: 2

Related Questions