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.

Possible duplicate of [Binding parameter as PostgreSQL array](http://stackoverflow.com/questions/18658107/binding-parameter-as-postgresql-array) — marcospereira, Feb 27 '16 at 13:19
Some notes: probably you should use the interface classes in Java like [java.sql.Array](http://docs.oracle.com/javase/8/docs/api/java/sql/Array.html) and not the internal PostgreSQL implementation. Second: the internal `javadoc` for PosgreSQL JDBC is [here](https://jdbc.postgresql.org/development/privateapi/index.html). Third: PostgreSQL have good native support for `JSON` - [data types](http://www.postgresql.org/docs/current/static/datatype-json.html) and [functions to manipulate them](http://www.postgresql.org/docs/current/static/functions-json.html). — zloster, Feb 27 '16 at 13:34
zloster, first: i want to use internal postgrSQL implementation because it's much simpler to write '{"my", "str", "array"}' then fight with java types, trying to cast from scala types to java just in order to update column. second: thanks for link. third: i already know that postgres has native json support, so what do u suggest? Forget about array native type and keep any data in json/jsonb fields? fourth: i'm not the only one who use PGObject, look at http://stackoverflow.com/questions/19599346/inserting-json-objects-in-postgresql-json-fields-with-anorm — Alexander Kondaurov, Feb 27 '16 at 13:52

score 2 · Accepted Answer · answered Mar 23 '16 at 23:19

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
  }

postgresql jdbc, pgobject available types, array type

1 Answers1