tminglei / slick-pg

Slick extensions for PostgreSQL
BSD 2-Clause "Simplified" License
838 stars 180 forks source link

org.postgresql.util.PSQLException: Unknown type CHAR(10)[] #310

Open bioball opened 8 years ago

bioball commented 8 years ago

I have column whose type is CHAR(10)[], and am attempting to use an AdavancedArrayJdbcType to create a mapping into a scala class.

Here's how I'm creating the mapping in my source code:

  implicit val blocklyChallengeIdArrayType = new driver.AdvancedArrayJdbcType[BlocklyChallengeId](
    "CHAR(10)",
    stringToBlocklyChallengeId,
    blocklyChallengeIdToString
  ).to(_.toSeq)

  def stringToBlocklyChallengeId(s: String) = utils.SimpleArrayUtils.fromString(BlocklyChallengeId.apply)(s).getOrElse(Nil)
  def blocklyChallengeIdToString(v: Seq[BlocklyChallengeId]) = utils.SimpleArrayUtils.mkString[BlocklyChallengeId](_.toString)(v)

However, I'm getting this error:

Unable to update coordinates org.postgresql.util.PSQLException: Unknown type CHAR(10)[].
    at org.postgresql.jdbc.PgPreparedStatement.setArray(PgPreparedStatement.java:1186) ~[postgresql-9.4.1211.jar:9.4.1211]
    at com.zaxxer.hikari.proxy.PreparedStatementJavassistProxy.setArray(PreparedStatementJavassistProxy.java) ~[HikariCP-java6-2.3.7.jar:na]
    at slick.jdbc.LoggingPreparedStatement.setArray(LoggingStatement.scala:91) ~[slick_2.11-3.1.1.jar:na]
    at com.github.tminglei.slickpg.array.PgArrayJdbcTypes$AdvancedArrayJdbcType.setValue(PgArrayJdbcTypes.scala:94) ~[slick-pg_core_2.11-0.14.3.jar:0.14.3]
    at com.github.tminglei.slickpg.array.PgArrayJdbcTypes$AdvancedArrayJdbcType.setValue(PgArrayJdbcTypes.scala:78) ~[slick-pg_core_2.11-0.14.3.jar:0.14.3]
    at com.github.tminglei.slickpg.array.PgArrayJdbcTypes$WrappedConvArrayJdbcType.setValue(PgArrayJdbcTypes.scala:119) ~[slick-pg_core_2.11-0.14.3.jar:0.14.3]
    at com.github.tminglei.slickpg.array.PgArrayJdbcTypes$WrappedConvArrayJdbcType.setValue(PgArrayJdbcTypes.scala:110) ~[slick-pg_core_2.11-0.14.3.jar:0.14.3]
        at slick.jdbc.BaseResultConverter.set(JdbcResultConverter.scala:18) ~[slick_2.11-3.1.1.jar:na]
    at slick.jdbc.BaseResultConverter.set(JdbcResultConverter.scala:10) ~[slick_2.11-3.1.1.jar:na]
    at slick.relational.ProductResultConverter.set(ResultConverter.scala:71) ~[slick_2.11-3.1.1.jar:na]
    at slick.relational.ProductResultConverter.set(ResultConverter.scala:46) ~[slick_2.11-3.1.1.jar:na]
    at slick.relational.TypeMappingResultConverter.set(ResultConverter.scala:138) ~[slick_2.11-3.1.1.jar:na]
    at slick.driver.JdbcActionComponent$InsertActionComposerImpl$SingleInsertAction$$anonfun$run$7.apply(JdbcActionComponent.scala:506) ~[slick_2.11-3.1.1.jar:na]
    at slick.driver.JdbcActionComponent$InsertActionComposerImpl$SingleInsertAction$$anonfun$run$7.apply(JdbcActionComponent.scala:504) ~[slick_2.11-3.1.1.jar:na]
    at slick.jdbc.JdbcBackend$SessionDef$class.withPreparedInsertStatement(JdbcBackend.scala:354) ~[slick_2.11-3.1.1.jar:na]
    at slick.jdbc.JdbcBackend$BaseSession.withPreparedInsertStatement(JdbcBackend.scala:407) ~[slick_2.11-3.1.1.jar:na]
    at slick.driver.JdbcActionComponent$ReturningInsertActionComposerImpl.preparedInsert(JdbcActionComponent.scala:636) ~[slick_2.11-3.1.1.jar:na]
    at slick.driver.JdbcActionComponent$InsertActionComposerImpl$SingleInsertAction.run(JdbcActionComponent.scala:504) ~[slick_2.11-3.1.1.jar:na]
    at slick.driver.JdbcActionComponent$SimpleJdbcDriverAction.run(JdbcActionComponent.scala:32) ~[slick_2.11-3.1.1.jar:na]
    at slick.driver.JdbcActionComponent$SimpleJdbcDriverAction.run(JdbcActionComponent.scala:29) ~[slick_2.11-3.1.1.jar:na]
    at slick.backend.DatabaseComponent$DatabaseDef$$anon$2.liftedTree1$1(DatabaseComponent.scala:237) ~[slick_2.11-3.1.1.jar:na]
    at slick.backend.DatabaseComponent$DatabaseDef$$anon$2.run(DatabaseComponent.scala:237) ~[slick_2.11-3.1.1.jar:na]
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) ~[na:1.8.0_05]
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) ~[na:1.8.0_05]
    at java.lang.Thread.run(Thread.java:745) ~[na:1.8.0_05]

Looks to me like the postgres JDBC is trying to find the type that corresponds to "CHAR(10)[]", and not finding any. Is this a bug? Or what should my SQL base type be? Is there something I can reference for this?

bioball commented 8 years ago

Update: I tried switching my type to "VARCHAR(10)" and get the same issue. I also tried just "CHAR", but this chops off my whole string and only inserts the first char.

tminglei commented 8 years ago

@bioball yes, postgres JDBC can't find char(10)[]. The (10) impacted the finding.

Maybe you can try varchar[] or text[].

bioball commented 8 years ago

Changing it to "VARCHAR" worked. This seems broken to me, but I guess it doesn't matter if I represent this column as a varchar or text? The column in my table is still a CHAR(10).

tminglei commented 8 years ago

Yeah, seems char(10)[] column don't reject values char[]/varchar[]/text[].

ploddi commented 7 years ago

Got same issue with interger type. Seems like more AdvancedArrayJdbcType related than concrete sqlBaseType.

tminglei commented 7 years ago

@ploddi you mean integer[] not work?

ploddi commented 7 years ago

I got very strange issue. Investigating it now. I implement AdvancedArrayJdbcType for my custom type (enumuratum enumerations). On SOME integers I getting https://github.com/pgjdbc/pgjdbc/blob/master/pgjdbc/src/main/java/org/postgresql/jdbc/PgPreparedStatement.java#L1098 exception and other enums works just fine. Mb some slick or postgres driver related issue.