3

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. – Evan Carroll Jun 23 '17 at 1:30
  • 1
    I don't see how comparing 2 to 'a' could possibly make any sense. – a_horse_with_no_name Jun 23 '17 at 5:21
  • 1
    And a check constraint is typically much better for this kind of value checking – a_horse_with_no_name Jun 23 '17 at 5:22
4

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,

  • 1
    BTW Why not just WHERE foo >= 'b'::stuff? – Abelisto Jun 23 '17 at 18:09
  • @Abelisto that's a much better idea, I've updated the answer. – Evan Carroll Jun 23 '17 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 '17 at 18:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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