How to query UUID for postgres

Question

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?

pozs · Accepted Answer · 2015-11-05 16:47:42Z

19

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'

answered Nov 5, 2015 at 16:47

pozs

36.6k5 gold badges61 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Zitao Xiong Over a year ago

This is cool! I was thinking to create a table with uuid and string of uuid so it uses normal searching. but your methods works very well. Thanks!

Zitao Xiong Over a year ago

Do you know the performance in Postgresql when using this kind comparison?

pozs Over a year ago

@ZitaoXiong it actually uses a simple btree (should be slightly faster than comparing texts, but that depends on a lot of things).

Timo Over a year ago

@ZitaoXiong I have tested this and confirmed that it successfully uses the index similar to how exactly equality would.

java-addict301 · Accepted Answer · 2022-10-05 19:30:07Z

5

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

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

Worked for me.

answered Oct 5, 2022 at 19:30

java-addict301

4,3182 gold badges31 silver badges40 bronze badges

Comments

Dmitry Sokurenko · Accepted Answer · 2015-11-05 16:37:06Z

4

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.

answered Nov 5, 2015 at 16:37

Dmitry Sokurenko

6,1324 gold badges34 silver badges54 bronze badges

2 Comments

Zitao Xiong Over a year ago

make sense, but I'd like to implement something like git's short charcters of SHA in postgresql. something like this: link

Dmitry Sokurenko Over a year ago

well then either just store the prefix as an interger / hex encoded string in an another column, or use the solution proposed by pozs.

Collectives™ on Stack Overflow

How to query UUID for postgres

3 Answers 3

4 Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related