How do I query PostgreSQL enums like in MySQL?

Question

With the following table in MySQL:

CREATE TABLE bob(foo ENUM('a','b','c'));

INSERT INTO bob (foo) VALUES ('a'),('b'),('c'),('a'),('a');

SELECT * FROM bob WHERE foo >= 2;
+------+
| foo  |
+------+
| b    |
| c    |
+------+

With the following table in PostGres:

CREATE TYPE stuff AS ENUM ('a', 'b', 'c');
INSERT INTO bob (foo) VALUES ('a'), ('b'), ('b'), ('c'), ('c');
SELECT * FROM bob WHERE foo > 2;
(HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.)

I've read that there are some fancy ways of getting around this problem, but is there a way to create a Type that mimics the MySQL capabilities? Or is there a data type with the capabilities that I want? (Ability to check string on insert, but also to make where cases against an aggregate).

Answer 1

I think you have a misconception about ENUM because of MySQL breaking the spec and being goofy. They're different in both MySQL and PostgreSQL, but they're more apparently different in PostgreSQL. You're trying to seamlessly query an internal representation detail. I'm actually glad PostgreSQL prevents it.

Instead use the ENUM support functions

CREATE TYPE stuff AS ENUM ('a', 'b', 'c');
SELECT * FROM enum_range('b'::stuff, NULL);

This returns everything from 'b' to the unbounded top of the enum (in this case 'c')

Now create a table and insert some values,

CREATE TABLE bob ( foo stuff );
INSERT INTO bob (foo) VALUES ('a'),('b'),('c'),('a'),('a');

Now to query it, do this

SELECT *
FROM bob
WHERE foo = ANY(enum_range('b'::stuff, NULL));

Or more simply as Abelisto suggested in the comments. Note that the details are still abstracted. You don't know what the sort order of b is,

WHERE foo >= 'b'::stuff;
WHERE foo >= 'b';

Enums are ordered by default. In PostgreSQL they're stored as int4. In MySQL they're stored as int2 or int1. However, their implementations should not be revealed to the user.

Just think, in your query above, reloading from a database after you add a value could potentially re-order the enums. Then your query breaks because you've assumed a specific value for 2.

See also,

• How does MariaDB handle ENUM types used in FOREIGN KEY constraints?
• How does MariaDB handle joins on ENUMs?