current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
stack overflow careers
Sign up ×
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them, it only takes a minute:
up vote 0 down vote favorite
1

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.

share|improve this question
    
What are the pg server and driver versions? – Radek Postołowicz Aug 14 at 20:08
    
The server is on 8.4.9 and as driver I'm using postgresql-9.4-1201.jdbc41.jar – panmari Aug 14 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 at 22:38

1 Answer 1

up vote 1 down vote accepted

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()));
    }
share|improve this answer
    
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 at 6: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 at 6:42
    
So how would I be able to do it if I cast everything to string in the query? – panmari Aug 15 at 8: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 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 at 15:09

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.