Search code examples
postgresqljsonb

Postgres JSONB specification for Copy In binary format

Im currently trying to optimise data loading in Postgres via JDBC. We are using COPY FROM STDIN with FORMAT 'binary' Now building the binary byte arrays is pretty straight forward for strings, longs, uuid's etc. However in one instance we have a JSONB field in the table and I have no idea how to serialize my json objects into the binary jsonb format. Is there any specification anywhere for jsonb?

Note: I have ruled out just sending a utf-8 binary serialized json string.

Solution

  • You just need to treat the json object as a normal string but adding a 1 (byte) before for the version number, which is the only version they currently support. Make sure as well you specify the length of the field is the "string.length" + 1 (for the version number)

    So, basically, if j is your json and dos is the output stream:

    val utfBytes = j.toString.getBytes("UTF8")
dos.writeInt(utfBytes.length + 1)
dos.write(Array(1.toByte))
dos.write(utfBytes)

    This is a comment from postgres source code (mirrored to github):

     /*
  104  * jsonb type recv function
  105  *
  106  * The type is sent as text in binary mode, so this is almost the same
  107  * as the input function, but it's prefixed with a version number so we
  108  * can change the binary format sent in future if necessary. For now,
  109  * only version 1 is supported.
  110  */