PostgreSQL, Npgsql returning 42601: syntax error at or near "$1"

Question

I'm trying to use Npgsql and/or Dapper to query a table and I keep running into Npgsql.PostgresException 42601: syntax error at or near "$1".

Here is what I've got trying it with NpgsqlCommand:

  using (var conn = new NpgsqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["postgres"].ConnectionString))
  {
    conn.Open();
    using (NpgsqlCommand command = new NpgsqlCommand("select * from Logs.Logs where Log_Date > current_date - interval @days day;", conn))
    {
      command.Parameters.AddWithValue("@days", days);
      var reader = command.ExecuteReader();

I've also tried it with Dapper(my preferred method) with:

  using (var conn = new NpgsqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["postgres"].ConnectionString))
  {
    conn.Open();
    var logs = conn.Query<Log>("select * from Logs.Logs where Log_Date > current_date - interval @days day;", new {days = days});

Either way I get the same Npgsql.PostgresException 42601: syntax error at or near "$1" error. The Statement in the Exception shows: select * from Logs.Logs where Log_Date > current_date - interval $1 day

Note, if I do the following it works fine, but it's not properly parameterized:

var logs = conn.Query<Log>("select * from Logs.Logs where Log_Date > current_date - interval '" + days + "' day;");

What am I doing wrong? I very much appreciate any feedback. Thank you.

Answer 1

PostgreSQL doesn't allow you to stick a parameter anywhere in a query. What you want can be achieved with the following:

var command = new NpgsqlCommand("select * from Logs.Logs where Log_Date > current_date - @days", conn))
command.Parameters.AddWithValue("@days", TimeSpan.FromDays(days));

This way you're passing the interval directly from Npgsql to PostgreSQL, rather than a part of the expression designed to create that interval.

Answer 2

i got this error using DapperExtensions

adding

DapperExtensions.DapperExtensions.SqlDialect = new PostgreSqlDialect();
DapperAsyncExtensions.SqlDialect = new PostgreSqlDialect();

before creating the connection fixed the issue

Answer 3

To subtract days from a date (assuming log_date is data type date), you can simplify:

"SELECT * FROM logs.logs WHERE log_date > CURRENT_DATE - @days;"

And provide @days as unquoted numeric literal (digits only) - which is taken to be an integer. This is even more efficient, since date - integer returns date, while date - interval returns timestamp.

The manual about interval input.