Is it possible cast enum to integer? Starting from 1 the first element
Add a comment
|
4 Answers
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
STABLEnotIMMUTABLE, 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
STRICTbecause 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.
3 Comments
Victor
Loving Postgres more and more!
Skartt
This solution only works since postgresql 9.1, enumsortorder is disponible since ths release
Craig Ringer
@Skartt Well, 9.1 is ancient and unsupported, and if you're using any older version you're years late for an upgrade...
You can do this if you're happy to use the function enum_range() and count over its results.
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.
3 Comments
Chris F Carroll
You say 'abuse' but I struggle to see in what way this is abuse ? The whole thing matches the Docs. (issue with removing values from an enum is an issue with just that, not an issue with your solution)
mpontes
I agree that "abuse" is too strong of a word, this solution follows from the docs as you said. The only thing is that each time you do this, you are effectively taking a potentially large portion of the whole enum definition and counting over it. This could be a total non-issue or not depending on what the PostgreSQL query executor does, I haven't looked at the source code or performed benchmarks on this solution.
JCCyC
This is perfect. Moreover, I see no reason why the Postgres developers couldn't just make enumfield::int mean ARRAY_LENGTH(ENUM_RANGE(NULL, enumfield), 1)
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.
answered Sep 10, 2012 at 3:36
Mike Sherrill 'Cat Recall'
96.6k20 gold badges134 silver badges195 bronze badges
Comments
I had enum type and needed to swap it for a FK (int4). The following worked for me
ALTER TABLE public."superhero"
ALTER COLUMN "power" TYPE int4
USING "power"::text::int4;
1 Comment
Matt Whitlock
Wouldn't that only work if your enumerator labels were already ASCII decimal numbers? If your goal is to drop the enum type altogether, then you could indeed rename all the enumerators to numbers and then use this cast to convert all columns of the enum type to integers before dropping the enum type, but that may not be what OP was trying to accomplish.