Re: Inconsistent Behavior in JSONB Numeric Array Deletion

On Jun 11, 2025, at 17:43, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

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

Ah. It’s a pity the existing operator behaves differently for different rhs operands. But maybe add a new one that’s defined to operator on contents rather than keys/indexes and deprecate (or un-document) the content behavior in the `-` operator?

Best,

David