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}] …

That I have, but now it’s not a problem, the function isn’t needed anymore.

Can you print the SQL it generates ?

It looks like the query it generates is hidden in the provided error message: [image] user974881: message: "syntax error at or near \"$0\"" query: "select url from pages where url not in $0 where host not in $0"

Whoops! Right so @user974881 you can’t use SQL in with parameters like that. Instead do: not host = any?(#{state.lock})

:waving_hand: Double where might also be a problem.

Hey there, as I said in the other thread Pattern match postgex result - (MatchError) no match of right hand side value: %Postgrex.Result - #14 by Schultzer this I would need a bit more time to fix, it’s not on you. I haven’t impl. double where in the sense that it would be accumulating ands. And I …

Well, I tried this: {query, params} = ~SQL[from pages] |> ~SQL[where not url = any?(#{state.lock}) and not host = any?(#{state.politeness})] |> ~SQL"select url" |> to_sql() {:ok, %Postgrex.Result{rows: [[response]]}} = Postgrex.query(:postgrex, query, params) so that I only …

If you are on main you might be able to get away with this: sql/test/string_test.exs at 35d743c7715da9336269dfd7ea8426f4d2fa7ec6 · elixir-dbvisor/sql · GitHub But your example might be an edge case that I haven’t gotten to yet.

now 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: "ER…

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 comfo…

I’m not using any, I’m using in(which then is converted to any by sql), and in the code I used before I do not see the extra space. And I do think it is better I use sql as the problem is more with elixir than with SQL.

Elixir Programming Language Forum

Postgrex error with sql library: message: "syntax error at or near \"$0\""

Questions & Help Questions

ruslandoga March 20, 2025, 11:35pm 5

Double where might also be a problem.