AE
Ash Elixir•2y ago
Jason

(Postgrex.Error) ERROR 42P18 (indeterminate_datatype) could not determine data type of parameter $1

Gotting the above error while try this code for a regex search. Ash.Query.filter(fragment("task_id ~* '?\\d+'", ^prefix)) The Ecto doc suggests explicitly defining type withtype(^prefix, :string) but I'm not sure how it's supposed to fit in as an Ash.Query. expression.
6 Replies
ZachDaniel
ZachDaniel•2y ago
You can use type/2 just like you have it Would also suggest pulling task_id out as a reference Ash.Query.filter(fragment("? ~* '?\\d+'", task_id, type(^prefix, :string)))
Jason
JasonOP•2y ago
It translates to this SQL statement, and I still get "could not determine data type of parameter $1" 
WHERE (t0."task_id"::text ~* '$1::text::text\d+') ORDER BY t0."task_id" DESC LIMIT $2["T", 1]

dbg #=>
  filter: #Ash.Filter<fragment(
    {:raw, ""},
    {:expr, task_id},
    {:raw, " ~* '"},
    {:expr, type("T", :string, [])},
    {:raw, "\\d+'"}
  )>
WHERE (t0."task_id"::text ~* '$1::text::text\d+') ORDER BY t0."task_id" DESC LIMIT $2["T", 1]

dbg #=>
  filter: #Ash.Filter<fragment(
    {:raw, ""},
    {:expr, task_id},
    {:raw, " ~* '"},
    {:expr, type("T", :string, [])},
    {:raw, "\\d+'"}
  )>
ZachDaniel
ZachDaniel•2y ago
🤔 That’s pretty strange… Try using ::text in the fragment
Jason
JasonOP•2y ago
Same error with the query repeating ::text 3 times. 
WHERE (t0."task_id"::text ~* '$1::text::text::text\d+') ORDER BY t0."task_id" DESC LIMIT $2 ["T", 1]
WHERE (t0."task_id"::text ~* '$1::text::text::text\d+') ORDER BY t0."task_id" DESC LIMIT $2 ["T", 1]
If I run where task_id::text ~* '^T::text\d+' in pgadmin, it returns nothing because ::text is treated as part of the regex pattern. That makes me think type(^prefix, :string)` is not going to work, even if the error message goes away.
ZachDaniel
ZachDaniel•2y ago
Oh, yeah okay I see I think the issue is the quotes Try something like ?::text || \d+ In the sql fragment I mean
Jason
JasonOP•2y ago
Then I get " (Postgrex.Error) ERROR 42601 (syntax_error) syntax error at or near "" 
WHERE ((t0."task_id"::text ~* $1::text||+)) ORDER BY t0."task_id" DESC LIMIT $2 ["T", 1]
WHERE ((t0."task_id"::text ~* $1::text||+)) ORDER BY t0."task_id" DESC LIMIT $2 ["T", 1]
|> Ash.Query.filter(fragment("(? ~* ?::text\d+)", task_id, ^prefix)) This works!! So the concatenation was the key. Thanks! ``` |> Ash.Query.filter(fragment("? ~* ('^'?::text||'\d+')", task_id, ^prefix)) ```

Did you find this page helpful?