Question 417061 in PostgreSQL Question posted in
The official documentation can be found here.

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.

Tags:

2

Answers


  1. 0

    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))
    Login or Signup to reply.
  2. 0
    1. First of all, the necessary information for database connection is prepared.
    2. Create a prepare statement and specify a placeholder to insert the data.
      3.Definitions of the integer array values to insert.
    3. Use the PgArray class to create an array object compatible with PostgreSQL.
      5.Use the setObject method to set the array parameters.
    4. Finally, the update operation is performed.
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search