Search code examples
ruby-on-railspostgresqlactiverecord

How to query UUID for postgres

I'd like to use UUID as an identifier, provide the first 8 digits to find out if it exists in the database.

normally I can do this without a problem:

select * from TABLE where id = 'e99aec55-9e32-4c84-aed2-4a0251584941'::uuid

but this gives me error:

select * from TABLE where id LIKE 'e99aec55%@'::uuid

error: 

ERROR:  invalid input syntax for uuid: "e99aec55%@"
LINE 1: select * from TABLE where id LIKE 'e99aec55...
                                              ^
Query failed
PostgreSQL said: invalid input syntax for uuid: "e99aec55%@"

Is there a way to query first n digits for a UUID type in postgresql?

Solution

  • Since you are searching for the highest bits of uuids, you can actually use between (because uuid comparison is well-defined in PostgreSQL):

    ...
where some_uuid between 'e99aec55-0000-0000-0000-000000000000'
                    and 'e99aec55-ffff-ffff-ffff-ffffffffffff'