operator does not exist: text = bytea

Update:

Followed this thread
PostgreSQL: Re: Null bind variable in where clause
</messages/by-id/4906DD3E-322A-4E26-8600-B967AFA8A58F@fastcrypt.com&gt;

and set *transform_null_equals to ON* in the parameter group on the AWS
Console. But no luck.

We are using Aurora PostgresSQL v13.3

On Wed, Jul 20, 2022 at 3:02 PM Karthik K L V <venkata.karthik4u@gmail.com>
wrote:

Hi Team,

I am getting the below error while executing a Select query using Spring
DataJPA and Hibernate framework in Aurora Postgres SQL.

*Caused by: org.postgresql.util.PSQLException: ERROR: operator does not
exist: text = bytea Hint: No operator matches the given name and argument
types. You might need to add explicit type casts. Position: 1037*

We have a query with bind value which sometimes gets resolved to null (no
data) depending on the application scenario.
The datatype of the bindvalue and the corresponding column is String.
The same query executes fine when the value of the bindvalue is populated.

Could you please let me know how I can resolve this issue without making
changes to the query?
Is there any configuration available in the Postgres Driver or on the
Postgres DB Server which can be set to resolve null bind values?

Appreciate your help. Thank you.

--
Karthik klv

--
Karthik klv

On Wed, Jul 20, 2022 at 03:02:13PM +0530, Karthik K L V wrote:

*Caused by: org.postgresql.util.PSQLException: ERROR: operator does not
exist: text = bytea Hint: No operator matches the given name and argument
types. You might need to add explicit type casts. Position: 1037*
Could you please let me know how I can resolve this issue without making
changes to the query?

I don't think it's possible.

bytea is basically array of bytes.
text on the other hand is array of characters.

Do the bytes "\xbf\xf3\xb3\x77" equal text "żółw"?

They actually kinda do, if we assume the bytes are text in encoding
Windows-1252 - in which case the bytes mean "żółw".

But if we'd assume the encoding to be, for example, iso8859-1, then the
same sequence of bytes means "¿ó³w"

That's why you can't compare bytes to characters.

You have to either convert bytes to text using convert or convert_from
functions, or change text into bytea using convert_to.

In some cases you can simply cast text to bytea:

$ select 'depesz'::text::bytea;
bytea
────────────────
\x64657065737a
(1 row)

which will work using current server encoding, afair.

depesz

Hi depesz,

Thanks for your reply. But, this issue is happening only when the bind
value of the query resolves to null. I am not trying to compare text to
bytes.
And the same query works fine when the bind value gets resolves to some
String. So, looking for an option which can tell Postgres Engine to read *=
null* as *is null*.

On Wed, Jul 20, 2022 at 5:29 PM hubert depesz lubaczewski <depesz@depesz.com>
wrote:

On Wed, Jul 20, 2022 at 03:02:13PM +0530, Karthik K L V wrote:

*Caused by: org.postgresql.util.PSQLException: ERROR: operator does not
exist: text = bytea Hint: No operator matches the given name and

argument

types. You might need to add explicit type casts. Position: 1037*
Could you please let me know how I can resolve this issue without making
changes to the query?

I don't think it's possible.

bytea is basically array of bytes.
text on the other hand is array of characters.

Do the bytes "\xbf\xf3\xb3\x77" equal text "żółw"?

They actually kinda do, if we assume the bytes are text in encoding
Windows-1252 - in which case the bytes mean "żółw".

But if we'd assume the encoding to be, for example, iso8859-1, then the
same sequence of bytes means "¿ó³w"

That's why you can't compare bytes to characters.

You have to either convert bytes to text using convert or convert_from
functions, or change text into bytea using convert_to.

In some cases you can simply cast text to bytea:

$ select 'depesz'::text::bytea;
bytea
────────────────
\x64657065737a
(1 row)

which will work using current server encoding, afair.

depesz

--
Karthik klv

Karthik K L V <venkata.karthik4u@gmail.com> writes:

We have a query with bind value which sometimes gets resolved to null (no
data) depending on the application scenario.
The datatype of the bindvalue and the corresponding column is String.
The same query executes fine when the value of the bindvalue is populated.
Could you please let me know how I can resolve this issue without making
changes to the query?

This seems like it depends on the details of how the JDBC driver sends
the bound parameter to the server, so you'd be better off asking in
the pgsql-jdbc mailing list (and supplying some code details).

It's entirely possible that there isn't any solution other than fixing
your app to be more consistent about how it binds the parameter.

regards, tom lane