Select rows using part of UUID4

Question

I have table of users with UUIDv4 as primary key. How can I select all rows with id starting with 'e2eb5'?

I tried following select:

SELECT * FROM "user" WHERE "id" LIKE 'e2eb5%';

In my application there are less than one thousand users and first part of UUID should be just all info you need to identify them.

Therefore I want user detail to be on url like this:

/users/e2eb5

Instead of:

/users/3b0fbfd6-0661-4880-b5c5-4659ed85fa96

Edit:

Querying it as suggested here: How to query UUID for postgres

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

is not viable solution as it requires either fixed length of uuid prefix or writing more complex query.

Possible duplicate of [How to query UUID for postgres](https://stackoverflow.com/questions/33549747/how-to-query-uuid-for-postgres) — Sergio Tulentsev, Mar 27 '18 at 15:31
You could try converting `id` to text and comparing it that way. `... WHERE "id"::text LIKE 'e2eb5%';`. Can lose performance with tons of rows, but I think should be okay with less than 1,000 rows. — trs, Mar 27 '18 at 15:48

score 2 · Accepted Answer · answered Mar 27 '18 at 15:52

You can convert the id to text and compare how you have in your question.

SELECT *
FROM "user"
WHERE "id"::text LIKE 'e2eb5%';

This needs to convert each row's id to text, so it can be slow with tons or rows. But working with less than 1,000 should be fine.

1 Answers1