Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?

like image 318
Zitao Xiong Avatar asked Nov 05 '15 16:11

Zitao Xiong


People also ask

What UUID Does Postgres use?

This function returns a version 4 (random) UUID. This is the most commonly used type of UUID and is appropriate for most applications. The uuid-ossp module provides additional functions that implement other standard algorithms for generating UUIDs.

How does Postgres store UUID?

PostgreSQL allows you store and compare UUID values but it does not include functions for generating the UUID values in its core. Instead, it relies on the third-party modules that provide specific algorithms to generate UUIDs.

How do I cast to UUID?

You should use the ->> operator instead as it will return an integer or text value. The returned text will not include the quotes. You can then cast the text to a uuid and Postgres will recognize it.

Is UUID auto generated Postgres?

Postgres natively supports UUID as a data type, even capable of being indexed and used as primary key. But to generate a UUID value, such as to establish a default value for a column, you need a Postgres extension (a plugin).


2 Answers

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'
like image 61
pozs Avatar answered Sep 18 '22 17:09

pozs


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.

like image 45
Dmitry Sokurenko Avatar answered Sep 19 '22 17:09

Dmitry Sokurenko