Postgresql array_agg of multiple columns with JDBC

Question

I'm trying to join a table that might have multiple entries for the given id and aggregate the rows corresponding to this id in an array. This looks as follows in the SQL query:

SELECT * from data
LEFT JOIN (select id, array_agg(row(foo, bar)) AS foo_bar_data from foo_bar_table group by id) AS temp using(id)

This works as intended, but I'm having trouble reading out the result in JDBC.

ResultSet rs = st.executeQuery(...)
Array a = rs.getArray("foo_bar_data")
// Now I want to iterate over the array, reading the values foo and bar of each item.

My efforts so far always ended in a Method org.postgresql.jdbc4.Jdbc4Array.getArrayImpl(long,int,Map) is not yet implemented. exception. How can I iterate over a, retrieving the values foo and bar?

Edit: I should possibly also mention, that foo and bar don't have the same type.

The server is on 8.4.9 and as driver I'm using postgresql-9.4-1201.jdbc41.jar — panmari, Aug 14 '15 at 20:23
Could you post the full query you're using (or at least the `SELECT` part, which is what the `ResultSet` reflects). — Mick Mnemonic, Aug 14 '15 at 22:38

sibnick · Accepted Answer · 2015-08-15T17:00:41.760

2

Postgres JDBC driver does not support anything except basic types (numbers, date/timestamp, string) as JDBC array. You can call array_agg twice and get two arrays on each row:

    try (Connection db = DriverManager.getConnection("jdbc:postgresql://localhost:5432/postgres", "postgres", "postgres");
                 ResultSet rs = db.createStatement().executeQuery("select array_agg(i), array_agg(s) from (select 1 i, 'a' s union select 2 i, 'b' s) t")) {
        rs.next();
        System.out.println(Arrays.toString((Object[]) rs.getArray(1).getArray()));
        System.out.println(Arrays.toString((Object[]) rs.getArray(2).getArray()));
    }

edited Aug 15 '15 at 17:00

answered Aug 15 '15 at 05:50

sibnick

3,995
20
20

I don't see how this query should work. Could you please explain a bit and possibly use the same column names as I did? Additionally, I mentioned in my answer that `foo` and `bar` don't have the same type, e. g. string resp. integer. Would this still work, as I see you cast everything to an Integer array? – panmari Aug 15 '15 at 06:11
Ok. I understand your problem. postgres JDBC does not support mixed array. You can select two columns: array of numbers, and array of strings. – sibnick Aug 15 '15 at 06:42
So how would I be able to do it if I cast everything to string in the query? – panmari Aug 15 '15 at 08:00
You can write: `SELECT * from data LEFT JOIN (select id, array_agg(row(foo, bar)::text) AS foo_bar_data` and you will get String[] like [(1,a), (2,b)], where (1,a) is one string. But why do not want use two columns/arrays? – sibnick Aug 15 '15 at 14:06
That's the solution I'm currently using, then I parse the string in java (very ugl). Yes, I'd prefer to get these two values individually. What do you mean by "select two columns"? To call array_agg twice, once with the string column and once with the int column? – panmari Aug 15 '15 at 15:09
Yes, call array_agg twice – sibnick Aug 15 '15 at 15:18
Ok, according to [this answer](http://stackoverflow.com/questions/7317475/postgresql-array-agg-order), the corresponding values will be ordered the same in both arrays, which would be enough for my use. Could you please edit your answer a bit, I'll accept it then. – panmari Aug 15 '15 at 15:28

Postgresql array_agg of multiple columns with JDBC

1 Answers1