postgresql jdbc, pgobject available types, array type

Question

I use postgresql 9.5 with jdbc driver 9.4.1208.jre7 and scalikejdbc wrapper

My table is:

CREATE TABLE refs
(
  name character varying NOT NULL,
  custom json,
  prices integer[]
)

I can insert json values using org.postgresql.util.PGobject:

val res = new PGobject()
res.setType("json")
res.setValue("{'name': 'test'}")
res

I also want to insert arrays. How can i do this? I thought that this would work:

  def toArrStr(a: Iterable[String]): PGobject = {
    val res = new PGobject()
    res.setType("ARRAY")
    res.setValue(s"{${a.map(i => '"' + i + '"').mkString(",")}}")
    res
  }

But it gives me exception: org.postgresql.util.PSQLException: Unknown type ARRAY

May be i'm missing smth but i can't find good docs about PGObject class. I think that PGObject class was designed exactly for purposes like mine but it doesn't behaves as expected

POSTGRES has many types, not only array but date, datetime, daterange, timestamprange, so on. I believe that there should be type names for its corresponding types.

Answer 1

I understood how to save character list[] using PGObject:

  def toArrStr(a: Iterable[String]): PGobject = {
    val res = new PGobject()
    res.setType("varchar[]")
    res.setValue(s"{${a.map(i => '"' + i + '"').mkString(",")}}")
    res
  }

To save array of numbers: where size is 2,4,8 (smallint, int, bigint)

  def toArrNum(a: Iterable[AnyVal], size: Int): PGobject = {
    val res = new PGobject()
    res.setType(s"int$size[]")
    res.setValue(s"{${a.mkString(",")}}")
    res
  }