How can we insert values of a field of data type text[] (array) of a PostgreSQL table

[milandalsaniya]

Hello All,

I am have field/column in a table of PostgreSQL DB of type text[]

I have tried both executeUpdate and executeBatch using addParameter on Query instance.
From exception throw and looking at lib code it's understandable that executeBatch does not support array.
executeUpdate does support array but what I understood by looking at code is that it only work for select query with where has "IN" operator.

If I want to insert array val into table, how can we do that in sqlo2? Is there any work around for that?

[aaberg]

Hi @milandalsaniya!
That is not supported in the current code base. There is an addParameter that takes an array. But as you correctly point out, it is a support method for the IN operator, which is pretty stupid. This is a good candidate for future improvement, I think.

[milandalsaniya]

Thank you for your response!

I have found the work around for insert and select for Array types.

For insert I am using CAST of pgsql text[] to varchar in DB level and supplying values as {"ab", "acc", "xyz"} as String in sql2o insert query.

For select of array type which is returned as PgArray type by JDBC driver does not directly work with Java/Kotlin List and Array. So for it I used custom conversion extending Quirks.

Both of them works for me.

Support for array insertion would be good addition to future release. I am hoping that it would be taken up. Makes code more clean and easy to implement.

[aaberg]

Nice, thanks for posting your solution 👍🏼