1

it is query code.

@Query(value = "select * from lb.relationship where serverid=?1 and serviceid=?2 and port=?3", nativeQuery = true)
VipServiceToServerRelationship findByServerIdAndServiceIdAndPort(UUID svrId, UUID svcId, String port);

it is schema.

CREATE TABLE lb.relationship (
    id uuid NOT NULL,
    serverid uuid NOT NULL,
    serviceid uuid NOT NULL,
    port character varying NOT NULL
);

I try the cmd below directly in pg's console and get the right anwser

select * from lb.relationship  where serverid=('d5dd2a02-960b-4c7b-ad9c-92e3650ab763'::uuid) and serviceid=('83265e81-7e14-4ff0-a4cc-6228a0d25f0d'::uuid) and port='80'
or
select * from lb.relationship  where serverid='d5dd2a02-960b-4c7b-ad9c-92e3650ab763' and serviceid='83265e81-7e14-4ff0-a4cc-6228a0d25f0d' and port='80'

Error message:

operator does not exist: uuid = bytea

From other similar questions' answer, I still can't figure out my failure. I don't have extra " " or ";" in my sql.

Update

I try to run springdata directly which get the same failure.

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

– 徐松峰 12 hours ago

Solution

Once i modify the schema from uuid to string, everything is fixed

– 徐松峰 11 hours ago

FoggyDay
  • 11,962
  • 4
  • 34
  • 48
徐松峰
  • 31
  • 3
  • ```operator does not exist: uuid = bytea``` it is the error msg – 徐松峰 Jan 21 '20 at 06:11
  • 1
    1) One of the most important things to put in your post: *THE EXACT ERROR MESSAGE*. I took the liberty of doing this for you. 2) Look here: https://stackoverflow.com/a/19751613/3135317 – FoggyDay Jan 21 '20 at 06:17
  • @FoggyDay So how do you know this is the actual error message? The referenced question only deals with JDBC while the OP uses JPA. – Jens Schauder Jan 21 '20 at 06:41
  • thx, FoggyDay. I think the method of query is different between this case and the case in that issue. I have hard code the type uuid in the params which should be transported to the query cmd above the function. – 徐松峰 Jan 21 '20 at 06:41
  • hi Jens Schauder, this error msg showed on my intelJ when i run that function – 徐松峰 Jan 21 '20 at 06:42
  • This might be helpful: https://stackoverflow.com/questions/26674556/operator-does-not-exist-uuid-bytea-java-with-postgres – Jens Schauder Jan 21 '20 at 06:43
  • I try to run springdata directly which get the same failure. ** Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist: uuid = bytea Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts. ** – 徐松峰 Jan 21 '20 at 06:45
  • 1
    Once i modify the schema from uuid to string, everything is fixed – 徐松峰 Jan 21 '20 at 07:32

1 Answers1

2

Q: Have you tried an explicit type cast?

EXAMPLE: 

@Query(value = "select * from lb.relationship where serverid=CAST(?1 AS uuid) and serviceid=CAST(?2 AS uuid) and port=?3", nativeQuery = true)
FoggyDay
  • 11,962
  • 4
  • 34
  • 48