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.

Some notes: probably you should use the interface classes in Java like java.sql.Array and not the internal PostgreSQL implementation. Second: the internal javadoc for PosgreSQL JDBC is here. Third: PostgreSQL have good native support for JSON - data types and functions to manipulate them. — zloster
– zloster, Commented Feb 27, 2016 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 stackoverflow.com/questions/19599346/… — Aleksand Kondaurov
– Aleksand Kondaurov, Commented Feb 27, 2016 at 13:52

Aleksand Kondaurov · Accepted Answer · 2016-03-23 23:19:32Z

2

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
  }

answered Mar 23, 2016 at 23:19

Aleksand Kondaurov

4,2037 gold badges48 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

postgresql jdbc, pgobject available types, array type

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related