Ecto.Adapters.SQL.query fails with message: "syntax error at or near \"$1\"" pg_code 42601

Questions / Help
1

The following query:

Ecto.Adapters.SQL.query(Wsmig.Repo, "ALTER SEQUENCE public.customers_id_seq RESTART WITH $1" ,[1502])

fails with the following error:

    %Postgrex.Error{
       connection_id: 87118,
       message: nil,
       postgres: %{
         code: :syntax_error,
         file: "scan.l",
         line: "1150",
         message: "syntax error at or near \"$1\"",
         pg_code: "42601",
         position: "53",
         routine: "scanner_yyerror",
         severity: "ERROR",
         unknown: "ERROR"
       },
       query: "ALTER SEQUENCE public.customers_id_seq RESTART WITH $1"
     }}

Running it directly in pgadmin for example works fine

ALTER SEQUENCE public.customers_id_seq RESTART WITH 1502

This also works, but not the preferred way, obviously:

Ecto.Adapters.SQL.query(Wsmig.Repo, "ALTER SEQUENCE public.customers_id_seq RESTART WITH #{1502}" ,[])

Any of you know what I am doing wrong?

2

Some things in postgres cannot be parameterized. I was aware of table names, but given this is part of the ALTER SEQUENCE it might have the same fate. If that’s indeed the case then interpolation is the only way to do this.

Just a note you can write your code also as the shorter Wsmig.Repo.query(sql, params).

1 Like