PostgreSQL: Is it possible cast enum to integer?

Question

Is it possible cast enum to integer? Starting from 1 the first element

Answer 1

While you can't cast enum to integer as Catcall explained, you can use the PostgreSQL-specific and possibly not-version-to-version compatible pg_enum system catalog table to get an ordinal representation.

regress=# CREATE TYPE happiness AS ENUM ('happy', 'very happy', 'ecstatic');  regress=# select enumsortorder, enumlabel from pg_catalog.pg_enum  regress-# WHERE enumtypid = 'happiness'::regtype ORDER BY enumsortorder;  enumsortorder | enumlabel   ---------------+------------              1 | happy              2 | very happy              3 | ecstatic (3 rows) 

This looks easy, but it isn't. Observe:

regress=# ALTER TYPE happiness ADD VALUE 'sad' BEFORE 'happy'; regress=# ALTER TYPE happiness ADD VALUE 'miserable' BEFORE 'very happy'; regress=# SELECT * FROM pg_enum ;  enumtypid | enumsortorder | enumlabel   -----------+---------------+------------     185300 |             1 | happy     185300 |             2 | very happy     185300 |             3 | ecstatic     185300 |             0 | sad     185300 |           1.5 | miserable (5 rows) 

From this you can see that enumsortorder provides ordering, but no fixed 'distance'. If support for removing values from enums is ever added, it'll likely create 'holes' in the sequence, too.

To get the enum position you'll need to use the row_number() window function to get the ordering, and the pg_typeof to get the oid (regtype) of the enum type. You need this to make sure that you return the right ordinal when there are multiple enums with the same label.

This function does the job:

CREATE OR REPLACE FUNCTION enum_to_position(anyenum) RETURNS integer AS $$ SELECT enumpos::integer FROM (         SELECT row_number() OVER (order by enumsortorder) AS enumpos,                enumsortorder,                enumlabel         FROM pg_catalog.pg_enum         WHERE enumtypid = pg_typeof($1)     ) enum_ordering     WHERE enumlabel = ($1::text); $$ LANGUAGE 'SQL' STABLE STRICT; 

Note:

• It's STABLE not IMMUTABLE, because adding (or if support in Pg is later added, removing) values from enums would change the ordering and break indexes relying on the ordering; so
• You cannot use this in an index expression; and
• It's STRICT because it should return null for a null input

You can now use this function to CREATE CAST for specific enums to integer. You cannot create a generic cast for all enums to integer, because the anyenum pseudo-type cannot be used for casts. For example, if I want to allow the demo happiness to be cast to integer, I would write:

CREATE CAST (happiness AS integer) WITH FUNCTION enum_to_position(anyenum); 

after which I could successfully execute:

regress=# SELECT ('happy'::happiness)::integer;  int4  ------     2 (1 row) 

Note that this is probably an insane thing to do, is unsupported, and is quite likely a terrible idea. Your code must be aware that the ordinal values will change when you add or (if later supported) remove a value from the enum.

Indexes created based on this cast (only possible if the function is defined immutable) will begin producing crazy and wrong results if you change the definition of the enum (except by appending new values to the end of it) because PostgreSQL believes you when you say a function is immutable. Don't do that.