Postgresql Enum comparison not working on one specific type

Question

Can't seem to compare two (of the same) enum types. If I run the command...

SELECT 'test'::core.mytype = 'test'::core.mytype;

it gives the following error...

ERROR:  42725: operator is not unique: core.mytype = core.mytype
LINE 1: SELECT 'test'::core.mytype = 'test'::core.mytype;
                                   ^
HINT:  Could not choose a best candidate operator. You might need to add explicit type casts.
LOCATION:  op_error, parse_oper.c:718

If I try this on another Enum type I have, it works perfectly fine, just seems to be this particular type. Not only that, but I'm sure I've been using the comparison for a few weeks now in another function.

Is there something I'm missing, or may have possibly removed by accident?

Runs for me: dbfiddle.uk/… Maybe you could try to create a fiddle that shows the behaviour? — S-Man
– S-Man, Commented Jun 24, 2019 at 8:46
wish I could, but only seems to be this one particular enum type. I've even tried restarting the service just to be sure. — Guy Park
– Guy Park, Commented Jun 24, 2019 at 8:53
Also, there's no custom operators on it nor is it any different from all the other enum types that work. — Guy Park
– Guy Park, Commented Jun 24, 2019 at 8:54
can you show the type definition and the corresponding table definitions that are relevant for the query — S-Man
– S-Man, Commented Jun 24, 2019 at 8:58

Guy Park · Accepted Answer · 2019-06-25 02:28:40Z

1

So it seems that this was a result of a CAST function, which I still do not understand why.

There was an [implicit] CAST from the given type to JSON/JSONb. I am assuming that PostgreSQL was converting to JSONB/JSONB and comparing that and failing (which is understandable), but the error output mentioned nothing about JSON.

Anyway, after having removed the cast (or at least making it ON ASSIGNMENT), it seems to now work.

Crazy...but good to know.

answered Jun 25, 2019 at 2:28

Guy Park

1,0671 gold badge15 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgresql Enum comparison not working on one specific type

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related