arrays of composite types, and client drivers like JDBC

From: Rob Nikander <rob(dot)nikander(at)gmail(dot)com>
To: Postgres General <pgsql-general(at)postgresql(dot)org>
Subject: arrays of composite types, and client drivers like JDBC
Date: 2019-06-15 06:32:51
Message-ID: CAPRf5vnR5MoXT4tJ1m5XFZwfxJjDiF3ph-Zgh0Ed1PsFY+kepA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

I'm experimenting with Java client libraries (the usual JDBC and some other
async projects, eg [1]). So far, I'm not finding ways to select/read
composite types without ugly string parsing. The simple cases are okay, but
if I have a column that is an array of composites, the client library might
give me a string for a column value like the following, with no builtin way
to parse it.

{"(10,\"abc \"\" \"\" \"\"\",\"2019-06-14
18:16:48.067969\",t)","(11,foo,\"2019-06-14 18:16:48.067969\",f)"}

Maybe I'm missing the part of the JDBC API that I can use here.

If not, then I'm wondering: is there something inherent in the underlying
PG protocol that makes this difficult for all these client/driver
libraries? In other words, maybe the protocol is sending strings meant for
display, not for parsing as data?

I was hoping I'd find an API like...

create type foo as (age int, color text);
create table t (a foo, b foo[]);
....
var resultSet = conn.prepareStatement("select a,b from
t").executeQuery()
var foos = resultSet.getArray("b")
var foo = foos.getElement(0)
var age = foo.getInt(1)
var color = foo.getString(2)

thanks,
Rob

[1]: https://github.com/cretz/pgnio

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tiemen Ruiten 2019-06-15 08:49:38 Re: checkpoints taking much longer than expected
Previous Message Jean Louis 2019-06-15 06:19:57 how to concat/concat_ws all fields without braces