How to compare character varying (varcar) to UUID in PostgreSQL?

Question

Operator does not exist: character varying = uuid

Client id is UUId and should be why it is not working.

Where I am wrong, since I have tried almost everything I imagined.

SELECT * FROM "cobranca_assinatura"
INNER JOIN "cadastro_cliente" ON ("cobranca_assinatura"."cliente_id" = "cadastro_cliente"."id")
WHERE "cadastro_cliente"."nome" LIKE marcelo% ESCAPE '\'

[2019-03-21 14:40:34] [42883] ERROR: operator does not exist: character varying = uuid [2019-03-21 14:40:34] Dica: No operator matches the given name and argument type(s). You might need to add explicit type casts.

Answer 1

uuid is a specific datatype. To you it looks like text, but it's not. You cannot compare uuid using string functions (uuid like "abc%"), or compare it with text.

As Tamer suggests, you can cast it first, if you need to compare.

SELECT * 
FROM (SELECT 'A0EEBC99-9C0B-4EF8-BB6D-6BB9BD380A11'::uuid as my_uuid) foo 
WHERE my_uuid::text like 'a%'

For example, above I create a uuid by casting a string to uuid type. (You'll fail if you attempt to cast just any old string to uuid because 'abc' cannot be a uuid).

Then with a uuid item called 'my_uuid', I cast to back to a string to use string comparison. (Note the 'A' becomes 'a'!)

Answer 2

In java -> spring boot for JDBC template. I got the same issue. This is type-mismatch error, it's expecting UUID datatype but supplying String. So, I converted UUID from String using UUID.fromString() and using UUID type in prepared statement (SQL)

Example:

String testSelectQry = "SELECT * from facility_announcements where id=:announcementID";
SqlParameterSource params = new MapSqlParameterSource("announcementID", UUID.fromString("094b76da-4140-11eb-b139-0242ac11000f"));
namedParameterJdbcTemplate.query(testSelectQry, params, new FacilityAnnouncementMapper());