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.