Question 89403 in PostgreSQL Question posted in
The official documentation can be found here.

Postgres Interval with Dapper & npgsql not working – Postgresql

I am using Dapper with Postgres npgsql and I am having trouble executing an SQL command using INTERVAL.
The command works fine outside my code.

I get the error

Npgsql.PostgresException: ‘42601: syntax error at or near "$1"

POSITION: 85′

Position 85 is exactly where the parameter @ageInDays is located.

My code is

int ageInDays = 90;
string mysql = "DELETE FROM Monitorvalues m1 WHERE lastupdate < current_date - INTERVAL @ageInDays DAY";

using (var connection = GetConnection)
{
    var dynamicParameters = new DynamicParameters();
    dynamicParameters.Add("ageInDays", ageInDays);
    return await connection.ExecuteScalarAsync<int>(mysql, dynamicParameters);
}

What am I doing incorrect?

Tags:

2

Answers


  1. 0

    Current Npgsql doesn’t support parametrized INTERVAL clause. One possible solution here is to define a variable and then directly pass to it.

    var ageInDays = 90;
var interval = $"INTERVAL '{ageInDays} days'";
var query = $"DELETE FROM Monitorvalues m1 WHERE lastupdate < current_date - {interval}";
return await connection.ExecuteScalarAsync<int>(query);

    This should work without problems

    Login or Signup to reply.
  2. 0

    You need to parameterize the entire interval value, passing it as a TimeSpan:

    conn.Query("DELETE FROM Monitorvalues WHERE lastupdate < current_date - @Interval", new { Interval = TimeSpan.FromDays(2) });

    As an alternative, if you really need to create an interval based on a parameterized number of days, you can use the make_interval PG function for this:

    conn.Query("DELETE FROM Monitorvalues WHERE lastupdate < current_date - make_interval(days => @Days)", new { Days = 2 });

    PostgreSQL doesn’t allow parameterizing a literal expression (INTERVAL ...), that’s why your code didn’t work.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search