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

by

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’!)

Leave a Comment