Some map<X>Array not working

[momania]

I try to use arrays, and get stuck with using numeric arrays and string arrays. Double arrays work as specified in the test, but numeric, or strings fails with the following error: org.postgresql.util.PSQLException: ERROR: wrong element type

Column definition is a simple:

create table my_table (
    my_strings varchar[],
    my_numeric numeric[]
);

Rings a bell and am I overseeing something, or is this a missing feature? :-)

[bytefish]

Hmmm. No you are not missing something. 🤔 Can you write a Unit Test, that reproduces the bug? So I have a starting point for debugging. 💪

[bytefish]

So here is the first problem: The mapStringArray(...) method only maps to a PostgreSQL text column and not to a varchar:

Then I looked into the Object Identifier list of PgBulkInsert and saw... I didn't include a varchar at all. So yes, you have found a missing feature. 👍

[momania]

Ah great! I'll see if I can make the unit tests for the missing array mappings tomorrow, so you can make sure they all work 😄
Now that you hinted that it's all in the data type definition mapping, I'll have a look if I can include the fix too.

[bytefish]

I am extending it right now and wrote a Unit Test. Give me a minute. 🥇

[bytefish]

OK. 💪💪💪

I fixed it :

• https://github.com/bytefish/PgBulkInsert/blob/master/PgBulkInsert/src/test/java/de/bytefish/pgbulkinsert/pgsql/handlers/ArrayTypesTest.java

But I won't manage to release it today. Also there is one more thing in the API Design, that needs to be adressed.

[bytefish]

The thing is. Everything works fine with the current API, if you say mapStringArray and your column is of Type text[]. Things get hairy, when let's say your Postgres column is a varchar[] type, like in your case. How do you want to differentiate between the Java Type and the Postgres Type with a simple mapStringArray method - you can't.

So at least for arrays, users need to pass in the specific types using mapCollection. But that is not really intuitive and leads to errors. I am thinking about renaming the mapStringArray into two methods:

• mapTextArray
• mapVarCharArray

This looks good to me. People mapping their database know their types best.

Things get even more complicated with a numeric field. In Java you have BigDecimal to represent a decimal with a precision and scale. But I am sure a lot people have a Double / Double[] in their Java model and want to map those to a numeric type column in the database.

The question is: Should the PgBulkInsert account for this? I would say if you want to map to a Numeric, you should define a Java Model with a BigDecimal in it. I don't want to put too much logic into all this.

What do you think?