Postgres custom operator priority over built in?

Question

I recently wrote a blog post about creating a custom minus operator for PostgreSQL's JSONB type.

Basically, an operator was created like this:

CREATE OPERATOR - (
    PROCEDURE = jsonb_minus,
    LEFTARG   = jsonb,
    RIGHTARG  = jsonb )

However, as a commenter correctly pointed out, after this operator is created, the default JSONB - TEXT is not working anymore. This query results in an error:

SELECT '{"a":1, "b":{"c":123, "d":"test"}}'::jsonb - 'a'

results in invalid input syntax for type json. You have to explicitly cast the second argument to TEXT in order for it to work.

After dropping the custom operator, normal behaviour is restored, and the query above gives the desired result: {"b": {"c": 123, "d": "test"}}

Why would Postgres try casting to JSONB over the default TEXT when the explicit type is not specified? Can I avoid this?

Answer 1

The problem arises when you define your operator because of the operator type resolution rules described in the documentation.

In the example you give, the arguments of the - operator have types jsonb (left argument) and unknown (right argument). PostgreSQL's type unknown is the type assigned to string literals.

Now if your custom operator is defined, operator type resolution chooses your operator in step 2.a.:

2. Check for an operator accepting exactly the input argument types. If one exists (there can be only one exact match in the set of operators considered), use it.

   a. If one argument of a binary operator invocation is of the unknown type, then assume it is the same type as the other argument for this check. Invocations involving two unknown inputs, or a unary operator with an unknown input, will never find a match at this step.

Consequently, the unknown argument 'a' will be cast to jsonb, which results in the error you observe.