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

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
--- End of stack trace from previous location where exception was thrown
 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 
   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
dbCmd, T obj, Action`1 commandFilter)
dbCmd, T obj)
   at ServiceStack.OrmLite.OrmLiteExecFilter.Exec[T](IDbConnection 
dbConn, Func`2 filter)

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.

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