Ecto Query like with UUIDs

Questions / Help
,
1

Hello,
for my project I want to filter and query for a Table which uses UUIDs. My code looks like this

 def transaction_query_by_id(id) do
    term = "%#{id}%"
    from t in BusinessTransaction,
    where: like(t.id,^term)
  end

The Query looks fine but when i want to get the entries using Repo.all() i get the following error.

** (Postgrex.Error) ERROR 42883 (undefined_function) operator does not exist: uuid ~~ unknown

I would appreciate all help

2

Welcome to the community @DLazar. If you Ecto type (Postgres type) is Ecto.UUID then the underlying data is an integer, not a string.

The error message from Postgres is basically saying the Postgres like function doesn’t know how to operate on the two arguments you are passing. I suspect t.id is an Ecto.UUID which is an integer, and you are passing a string as the second argument.

I’m also don’t think the semantics of like on a UUID makes sense either in integer or string form but perhaps your use case adds some meaning not clear to me.

2 Likes
3

Thanks for the answer. Generated UUIDs look like the following in my Datatable ‘e149a7f5-b3e4-4ea9-acda-4a21ab7767c8’. I now want the user to input a string as a pattern to search for. For example “acda”, meaning it should give me all transactions whose Ids have somewhere “acda”.
You are right. I forgot the UUIDs are originally numbers which makes this a bit more complicated for me. I guess I have to cast it to a string, but I dont know exactly how.

4

You can specify/cast on the postgres side using field::type syntax and from Ecto with type/2

3 Likes