A PostgreSQL type with the name string[] was not found in the database

[SunilRathod]

I am using this to bulk Insert a records with a string array column type in postgreSQL. The same Entity column definition works when I don't use this library with EntityFramework's Add and SaveChanges methods However, when used BulkInsert, it Argument Exception

"System.ArgumentException: A PostgreSQL type with the name string[] was not found in the database at Npgsql.Internal.NpgsqlDatabaseInfo.GetPostgresTypeByName(String pgName) at Npgsql.TypeMapping.ConnectorTypeMapper.ResolveByDataTypeName(String typeName, Boolean throwOnError) at Npgsql.NpgsqlParameter.ResolveHandler(ConnectorTypeMapper typeMapper) at Npgsql.NpgsqlBinaryImporter.Write[T](T value, NpgsqlParameter param, Boolean async, CancellationToken cancellationToken) at EFCore.BulkExtensions.SQLAdapters.PostgreSql.PostgreSqlAdapter.InsertAsync[T](DbContext context, IList1 entities, TableInfo tableInfo, Action1 progress, Boolean isAsync, CancellationToken cancellationToken) at EFCore.BulkExtensions.SQLAdapters.PostgreSql.PostgreSqlAdapter.InsertAsync[T](DbContext context, IList1 entities, TableInfo tableInfo, Action1 progress, Boolean isAsync, CancellationToken cancellationToken) at EFCore.BulkExtensions.SQLAdapters.PostgreSql.PostgreSqlAdapter.InsertAsync[T](DbContext context, Type type, IList1 entities, TableInfo tableInfo, Action1 progress, CancellationToken cancellationToken) at EFCore.BulkExtensions.SqlBulkOperation.InsertAsync[T](DbContext context, Type type, IList1 entities, TableInfo tableInfo, Action1 progress, CancellationToken cancellationToken) at EFCore.BulkExtensions.DbContextBulkTransaction.ExecuteAsync[T](DbContext context, Type type, IList1 entities, OperationType operationType, BulkConfig bulkConfig, Action1 progress, CancellationToken cancellationToken)"

This is how the property is build in the entity entity.Property(p => p.abc) .HasColumnType("string[]");

Any Idea why this column type works in normal EFCore but not with this library's BulkInsert method?

[borisdj]

Array not yet supported.

[anchurcn]

Array not yet supported.

Is there any method to support fields of array type? Or I will

if(entities.Any())
{
 if(entities.First().GetType().IsClassThatContainsArrayMember())
 {
  dbContext.AddRange(entities);
  dbContext.SaveChanges();
 }
 else
 {
  dbContext.BulkInsert(entities);
 }
}

Is this the best method so far?

[borisdj]

That would be a fix for now.

[anchurcn]

Is possible to fix it? Lable with BUG or Enhancement, and give some tips if any, I will solve it when I have spare time.

[anchurcn]

I do have a solution for this. It's actually not a problem on EFCore.BulkExtensions but on Npgsql. Npgsql 6.x.x do not support the full list of postgre type names. eg: double precision[] is supported while float8[] is not. But Npgsql 7.x.x add a API GlobalTypeMapper.DataTypeNameToNpgsqlDbType for converting postgres type name to NpgsqlDbType. We can copy this api to EFCore.BulkExtensions and use the following lines on PostgreSqlAdapter.InsertAsync method.

var pgDbType = GlobalTypeMapper.DataTypeNameToNpgsqlDbType(columnType);
if (isAsync)
{
	await writer.WriteAsync(propertyValue, pgDbType , cancellationToken).ConfigureAwait(false);
}
else
{
	writer.Write(propertyValue, pgDbType );
}

It probably works fine. There's a overload API public void Write<T>([AllowNull] T value, NpgsqlDbType npgsqlDbType) working in Npgsql 6.x.x. This is my test case.

writer.Write(new []{"hello","world"}, NpgsqlTypes.NpgsqlDbType.Array| NpgsqlTypes.NpgsqlDbType.Text);

[anchurcn]

Use reflection to check there is an API DataTypeNameToNpgsqlDbType in Npgsql 6.0.7

            var npgsql = typeof(Npgsql.TypeMapping.PostgresTypeOIDs).Assembly;
            var t = npgsql.GetType("Npgsql.TypeMapping.GlobalTypeMapper");
            var m = t.GetMethod("DataTypeNameToNpgsqlDbType");
            var f = m.CreateDelegate<Func<string, NpgsqlTypes.NpgsqlDbType>>();
            var dbType = f("timestamp[]");
            var eql = dbType == (NpgsqlTypes.NpgsqlDbType.Timestamp | NpgsqlTypes.NpgsqlDbType.Array);

[borisdj]

I have added a Test: ArrayPGTest with one prop [Column(TypeName = "text[]")] public string[]? Formats { get; set; } and it passes with latest version.

UPDATE, or maybe this is an issue only for EF6.

[anchurcn]

it's on 6.x version of npgsql.

[borisdj]

Can you make a PR (on the master branch.) with the proposed fix?

[anchurcn]

sure