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).

  • I've removed the word Composite Type because that means something totally different in PostgreSQL and I don't believe that's what you want. Feel free to clarify. Jun 23, 2017 at 1:30
  • 1
    I don't see how comparing 2 to 'a' could possibly make any sense.
    – user1822
    Jun 23, 2017 at 5:21
  • 1
    And a check constraint is typically much better for this kind of value checking
    – user1822
    Jun 23, 2017 at 5:22

1 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

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,

  • 1
    BTW Why not just WHERE foo >= 'b'::stuff?
    – Abelisto
    Jun 23, 2017 at 18:09
  • @Abelisto that's a much better idea, I've updated the answer. Jun 23, 2017 at 18:11
  • 1
    And, according to my test, WHERE foo >= 'b' is enough :o) Almost same to the question but much more convenient.
    – Abelisto
    Jun 23, 2017 at 18:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.