PostgreSQL dialect missing array converters for decimal, float, double, datetime

ServiceStack.OrmLite PostgreSQL
1

Came into an issue with PostgreSQL/Npgsql native array types. Currently the dialect only handles arrays for int[], long[] and string[].

Error is trying can’t convert text into pg numeric[] from decimal[].
42804: column "latitudes" is of type numeric[] but expression is of type text

Npgsql.PostgresException (0x80004005): 42804: column "latitudes" is of 
type numeric[] but expression is of type text
   at 
Npgsql.NpgsqlConnector.<>c__DisplayClass161_0.<<ReadMessage>g__ReadMessageLong|0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown
 ---
   at 
Npgsql.NpgsqlConnector.<>c__DisplayClass161_0.<<ReadMessage>g__ReadMessageLong|0>d.MoveNext()
 in C:\projects\npgsql\src\Npgsql\NpgsqlConnector.cs:line 1032
--- End of stack trace from previous location where exception was thrown
 ---
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean 
isConsuming) in C:\projects\npgsql\src\Npgsql\NpgsqlDataReader.cs:line 
444
   at Npgsql.NpgsqlDataReader.NextResult() in 
C:\projects\npgsql\src\Npgsql\NpgsqlDataReader.cs:line 332
   at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior,
 Boolean async, CancellationToken cancellationToken) in 
C:\projects\npgsql\src\Npgsql\NpgsqlCommand.cs:line 1219
   at Npgsql.NpgsqlCommand.ExecuteNonQuery(Boolean async, 
CancellationToken cancellationToken) in 
C:\projects\npgsql\src\Npgsql\NpgsqlCommand.cs:line 1042
   at Npgsql.NpgsqlCommand.ExecuteNonQuery() in 
C:\projects\npgsql\src\Npgsql\NpgsqlCommand.cs:line 1025
   at 
ServiceStack.OrmLite.OrmLiteWriteCommandExtensions.Update[T](IDbCommand 
dbCmd, T obj, Action`1 commandFilter)
   at 
ServiceStack.OrmLite.OrmLiteWriteCommandExtensions.Save[T](IDbCommand 
dbCmd, T obj)
   at ServiceStack.OrmLite.OrmLiteExecFilter.Exec[T](IDbConnection 
dbConn, Func`2 filter)
   at

I was able to get it to work with by adding:

Add RegisterConverter<> to PostgreSqlDialectProvider.cs constructor.

RegisterConverter<decimal[]>(new PostgreSqlDecimalArrayConverter());

Add PostgreSqlDecimalArrayConverter class.

    public class PostgreSqlDecimalArrayConverter : ReferenceTypeConverter
    {
        public override string ColumnDefinition => "numeric[]";

        public override DbType DbType => DbType.Object;

        public override object FromDbValue(Type fieldType, object value)
        {
            return (decimal[])value;
        }

        public override object ToDbValue(Type fieldType, object value)
        {
            return (decimal[])value;
        }

       /*
        public override string ToQuotedString(Type fieldType, object value)
        {
            // other converters do this
            // var decimalArray = (decimal[])value;
            // return this.ToArray(decimalArray);

            // could probably do this, unless there is a reason for it
            return this.ToArray((decimal[])value);
        }
       */
    }

Note: I’m not sure if ToQuotedString() is needed as it works without it. Maybe that was the old way of doing it before Npgsql accepted those array types natively. I noticed it was in the other array converters.

It also looks like array converters are missing for float[], double[], and DateTime[].

I didn’t fully test, but changing the converter for each of those types seem to work.

The lines changed from the above code were:

RegisterConverter<float[]>(new PostgreSqlFloatArrayConverter())
public override string ColumnDefinition => "real[]";
return (float[])value;

RegisterConverter<double[]>(new PostgreSqlDoubleArrayConverter())
public override string ColumnDefinition => "double precision[]";
return (double[])value;

RegisterConverter<DateTime[]>(new PostgreSqlDateTimeArrayConverter())
public override string ColumnDefinition => "timestamp with time zone[]";
return (DateTime[])value;

Note: I used timestamp with time zone[] as the column definition for DateTime[] since that would probably be the most common scenario, not sure what to do if someone wanted date[] or timestamp[] instead or any of the other timestamp variations.

The Npgsql Supported Types and their conversions is here.

2

I’ve added the remaining numeric types in this commit, still only arrays use pgsql’s array types by default:

If you also want them to be used for List<T> types you can either register additional mappings for each List<T> type or you can the [PgSql*] attrs:

It’s a good idea to use the attributes anyway to convey that they are being stored in PostgreSQL’s array types.

This change is available from v5.4.1 that’s now available on MyGet.

1 Like