postgresqljdbc

How to Insert `int[]` type data into Postgresql using jdbc? (wrong array used)

The wxvc_collection column data type is int[], t_hot_hub_operation_item is table name.

fun main() {
    val connection = DriverManager.getConnection("jdbc:postgresql://localhost:5432/sage-dev", "postgres", "123456")
    val sql = "INSERT INTO t_hot_hub_operation_item (wxvc_collection) VALUES (?)"
    val statement = connection.prepareStatement(sql)
    val array = connection.createArrayOf("int4", arrayOf(listOf(1, 2, 3)))
    statement.setArray(1, array)
    statement.executeUpdate()
}

I referenced this answer but it seems already outdated.

The error message when running the code is as follows:

Exception in thread "main" org.postgresql.util.PSQLException: ERROR: invalid input syntax for type integer: "[1, 2, 3]"
  in location：unnamed portal parameter $1 = '...'
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2713)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2401)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:368)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:498)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:415)
    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:190)
    at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:152)
    at com.sage.server.TestKt.main(Test.kt:16)

Note: Try not to change the SQL statement, because I actually use the mybatis framework, which does not support modifying SQL statements.

Solution

  • I think you're passing a nested array (arrayOf(listOf(1, 2, 3))) instead of a flat array.
    PostgreSQL expects a single-dimensional int[].

    Try This:

    val array = connection.createArrayOf("int4", arrayOf(1, 2, 3))