PostgreSQL: Is it possible cast enum to integer?

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.

You can do this through a clever abuse of the function enum_range().

If you pass your enum value as the second argument of the enum_range() function, with NULL being the first, you'll get an array with all the values that enum can take up to that point. Then you just need to count them with array_length and you get an integer that represents the enum.

Here's an example. This is my enum:

content=# select enum_range(null::content_state);                        enum_range                          -------------------------------------------------------------- {created,deleted,preview,draft,submitted,approved,published}

And this is me figuring out the int for the "draft" value:

content=# select array_length(enum_range(NULL, 'draft'::content_state), 1); array_length --------------            4

Caveat: removing values from the enum will make your ints point to other values, so don't use this on enums that you might want to change at some point.

You can't cast an enum to integer.

You might be able to write a custom operator to extract the number associated with a value, but I find it hard to believe that's worth the trouble.

If I needed that kind of information, I'd have built a table and set a foreign key reference to it instead of using an enum.