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

Can you print the SQL it generates ?

It looks like the query it generates is hidden in the provided error message:

Whoops!

Right so @user974881 you can’t use SQL in with parameters like that. Instead do:

not host = any?(#{state.lock})

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 would say it’s an open question of how that should work.

I appreciate you’re taking the library for a ride, it gives me a clear picture of what your expectations are and where I need to improve.

Although I’m currently prioritizing a rewrite of the parser, since the first iteration has been handwritten and does not even parse DDL.

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 have one where and also for your solution, and now I get:

** (MatchError) no match of right hand side value: {:error, %Postgrex.Error{message: nil, postgres: %{code: :undefined_parameter, file: "parse_param.c", line: "140", message: "there is no parameter $0", pg_code: "42P02", position: "44", routine: "variable_paramref_hook", severity: "ERROR", unknown: "ERROR"}, connection_id: 4546, query: "select url from pages where not url = any ($0) and not host = any ($0)"}}

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: "ERROR"}, connection_id: 5621, query: "select url from pages where url not in $0 and host not in $1"}}

with

{query, params} = ~SQL[from pages]
      |> ~SQL[where url not in #{state.lock} and host not in #{state.politeness}]
      |> ~SQL"select url"
      |> to_sql()

    {:ok, %Postgrex.Result{rows: [[response]]}} = Postgrex.query(:postgrex, query, params)

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.

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.