Re: Inconsistent Behavior in JSONB Numeric Array Deletion

"David E. Wheeler" <david@justatheory.com> writes:
> Bear in mind that `-` currently does both. Of the three current variants, the first two delete from an array by
value:

> * jsonb - text: Deletes a key (and its value) from a JSON object, or matching string value(s) from a JSON array.

> * jsonb - text[] → jsonb: Deletes all matching keys or array elements from the left operand.

> * jsonb - integer → jsonb: Deletes the array element with specified index (negative integers count from the end).
Throwsan error if JSON value is not an array. 

> Before I went and looked it up, I was also thinking this could use a different operator. But it’s already a bit
overloaded,alas. So I could see the new behavior being: 

> * jsonb - jsonb → jsonb: Deletes the array element with specified value. Throws an error if JSON value is not an
array.


I fear that that would cause some problems.  Consider

regression=# select '["foo", "bar"]'::jsonb - 'bar';
 ?column?
----------
 ["foo"]
(1 row)

Right now we resolve the unlabeled literal as type text.
But if jsonb - jsonb existed, we'd decide it's jsonb, thanks
to the heuristic that prefers same-type-as-the-other-input
(rule 2a at [1]).  So it's pretty nearly certain that
adding jsonb - jsonb would break some existing queries;
or worse, silently cause them to do something different.
Maybe that's acceptable, but it's a demerit of this proposal.

            regards, tom lane

[1] https://www.postgresql.org/docs/current/typeconv-oper.html