CODEWITHSUNDEEP
ruby-on-railspostgresqlactiverecord
Zitao Xiong
Zitao Xiong

Reputation: 976

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?

Upvotes: 20

Views: 19801

Answers (3)

java-addict301
java-addict301

Reputation: 4106

Why not just cast your uuid column using id::varchar like so:

select * from TABLE where id::varchar LIKE 'e99aec55%'

Worked for me.

Upvotes: 5

pozs
pozs

Reputation: 36234

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'

Upvotes: 19

Dmitry Sokurenko
Dmitry Sokurenko

Reputation: 6132

UUIDs are not stored as strings in Postrges, they are stored as a 16-byte long binary values. So the only way to query it in the way you want is to convert it to string at first, but the performance of such conversion will be worser than just performing an equality comparison.

Also you will need to maintain an index on those string representation of the UUIDs, so it just doesn't make sense.

Upvotes: 4

Related Questions