Dapper Query with NaN values

Question

I have a table with a numeric column type that may contain NaN values. When running a query with Dapper I get an exception Unknown message code: 0 if the field contains NaN values.

Example schema

CREATE TABLE test (id Serial NOT NULL, val Numeric NOT NULL );
insert into test(val) VALUES(1.5),('Nan');

Here is the query that is throwing an exception

public class MyClass
{
    public int ID { get; set; }
    public double Val { get; set; }
}

public static async Task<IEnumerable<MyClass>> GetTest()
{
    var sql = "select * from test";
    using (var connection = Postgres.GetConnection())
    {
        try
        {
            var results = await connection.QueryAsync<MyClass>(sql);
            return results;
        }

        catch (Exception ex)
        {
            return new List<MyClass>();
        }
    }
}

The exception being thrown. (ex variable from catch)

Unknown message code: 0

And here is the stack trace.

   at Npgsql.PGUtil.ValidateBackendMessageCode(BackendMessageCode code) in C:\projects\npgsql\src\Npgsql\PGUtil.cs:line 88
   at Npgsql.NpgsqlConnector.<>c__DisplayClass161_0.<<ReadMessage>g__ReadMessageLong|0>d.MoveNext() in C:\projects\npgsql\src\Npgsql\NpgsqlConnector.cs:line 956
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Npgsql.NpgsqlDataReader.<NextResult>d__46.MoveNext() in C:\projects\npgsql\src\Npgsql\NpgsqlDataReader.cs:line 383
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Npgsql.NpgsqlDataReader.<Consume>d__64.MoveNext() in C:\projects\npgsql\src\Npgsql\NpgsqlDataReader.cs:line 703
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Npgsql.NpgsqlDataReader.<Close>d__68.MoveNext() in C:\projects\npgsql\src\Npgsql\NpgsqlDataReader.cs:line 740
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Npgsql.NpgsqlDataReader.Close() in C:\projects\npgsql\src\Npgsql\NpgsqlDataReader.cs:line 714
   at Npgsql.NpgsqlDataReader.Dispose(Boolean disposing) in C:\projects\npgsql\src\Npgsql\NpgsqlDataReader.cs:line 709
   at System.Data.Common.DbDataReader.Dispose()
   at Dapper.SqlMapper.<QueryAsync>d__33`1.MoveNext() in /_/Dapper/SqlMapper.Async.cs:line 459
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
   at MyClass.<GetTest>d__31.MoveNext() in C:\MyClass.cs:line 367

Answer 1

The Numeric type in Postgres does have the ability to contain NaN.

You can insert double.NaN just fine.

await connection.ExecuteAsync("INSERT INTO test(val) VALUES(@val)", new { val = double.NaN });

Or like this: (note that single quotes are required around the NaN literal)

INSERT INTO test(val) VALUES('NaN');

However the problem is that Numeric type matches the decimal type in C# which does not support NaN. Apparently Dapper internally tries setting a decimal property to NaN even though the property on my class is double. This is what causes the error.

The solution is to change the data type in the database to double DOUBLE PRECISION or REAL

Alternately if changing the schema is not an option you can explicitly cast to DOUBLE PRECISION in the query.

SELECT id, val::DOUBLE PRECISION FROM test