BUG #18222: Unexpected Error--Cannot delete from scalar

On Mon, 2023-12-04 at 05:56 +0000, PG Bug reporting form wrote:

PostgreSQL version: 16.1

SELECT ('1'-to_hex(2))::BOOLEAN; -- ERROR: cannot delete from scalar

This error looks strange to me as I do not understand what this error
means.

If I execute the following query, there is no error instead:
SELECT '1'-2; -- {-2}

That's not a bug.

PostgreSQL infers the operator jsonb - text. '1' gives a valid "jsonb"
scalar, but you cannot remove an attribute from a JSON scalar.

I cannot tell you what to do, because your expression makes as little
sense to me as it does to PostgreSQL.

Yours,
Laurenz Albe

PG Bug reporting form <noreply@postgresql.org> writes:

SELECT ('1'-to_hex(2))::BOOLEAN; -- ERROR: cannot delete from scalar

This error looks strange to me as I do not understand what this error
means.

Just out of curiosity, what did you expect that to do?

to_hex() produces type text, and there's no "text - text" operator.
What's evidently happening is that the parser finds "jsonb - text"
(i.e., jsonb_delete) as the only potentially applicable operator.
It successfully coerces '1' to a scalar jsonb value, but then
jsonb_delete() spits up because it's expecting an array.

I'm not sure that it was such a great idea to define '-' as having
this meaning for jsonb, but it's been there awhile now so probably
too late to change.

regards, tom lane

I thought it would probably give a warning that no available operator for text-text, not this error.
Anyway, thanks!
________________________________
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Monday, December 4, 2023 10:15:46 PM
To: Ba Jinsheng <bajinsheng@u.nus.edu>
Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #18222: Unexpected Error--Cannot delete from scalar

- External Email -

PG Bug reporting form <noreply@postgresql.org> writes:

SELECT ('1'-to_hex(2))::BOOLEAN; -- ERROR: cannot delete from scalar

This error looks strange to me as I do not understand what this error
means.

Just out of curiosity, what did you expect that to do?

to_hex() produces type text, and there's no "text - text" operator.
What's evidently happening is that the parser finds "jsonb - text"
(i.e., jsonb_delete) as the only potentially applicable operator.
It successfully coerces '1' to a scalar jsonb value, but then
jsonb_delete() spits up because it's expecting an array.

I'm not sure that it was such a great idea to define '-' as having
this meaning for jsonb, but it's been there awhile now so probably
too late to change.

regards, tom lane