user974881
Postgrex error with sql library: message: "syntax error at or near \"$0\""
Hello, I am trying to do a postgrex query with the sql library where I get the first entry from a table sorted by last_visited and excluding all entries where url is in state.lock or host is in state.politeness.
{query, params} = ~SQL[from pages]
|> ~SQL[where url not in #{state.lock}]
|> ~SQL[where host not in #{state.politeness}]
|> ~SQL"select url"
|> to_sql()
{:ok, %Postgrex.Result{rows: [[response]]}} = Postgrex.query(:postgrex, query, params)
However, when I run this I get:
** (MatchError) no match of right hand side value: {:error, %Postgrex.Error{message: nil, postgres: %{code: :syntax_error, file: "scan.l", line: "1244", message: "syntax error at or near \"$0\"", pg_code: "42601", position: "40", routine: "scanner_yyerror", severity: "ERROR", unknown: "ERROR"}, connection_id: 7202, query: "select url from pages where url not in $0 where host not in $0"}}
Does anyone know how to fix this?
Marked As Solved
user974881
That I have, but now it’s not a problem, the function isn’t needed anymore.
Also Liked
benwilson512
Whoops!
Right so @user974881 you can’t use SQL in with parameters like that. Instead do:
not host = any?(#{state.lock})
ruslandoga
Double where might also be a problem.
benwilson512
You added a space that shouldn’t be there after any.
Taking a step back, are you sure this is the right library for you? The sql library seems aimed at people familiar with SQL and who prefer to use that syntax instead of the ecto query syntax. It doesn’t seem like this is syntax you’re super comfortable with though and the process of using ecto would be a lot simpler for what you’re doing.
christhekeele
It looks like the query it generates is hidden in the provided error message:
benwilson512
Your problem is the SQL. Take the SQL you are writing and put it in Postgres, you will get an error. Make sure to use parameters and not just write out the IN manually.
You can’t use parameters with IN like that; this is a Postgres limitation not an Elixir one. This is why you should be using any and not in when using parameters.
Popular in Questions
Other popular topics
Latest on Elixir Forum
Sponsor Spotlight
Supporting innovation across the BEAM ecosystem.
Categories:
Sub Categories:
Forums
Our Sponsors
Build Elixir applications with speed and confidence.
Supporting innovation across the BEAM ecosystem.
We build reliable cloud platforms for business-critical systems.
Catch errors, track performance, monitor hosts and more.
Develop your skills with books, videos, and courses.
Producing high quality Elixir screencasts since 2017.
The deployment platform built for Elixir: PaaS ease, VPS control.
Courses that'll move you from confusion to "Aha, now I get it!"