Postgres Interval with Dapper & npgsql not working

Question

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?

Answer 1

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