Certain format select with group by in postgresql

Question

so I have to write a CSV in Java by reading a query in PostgreSQL. The problem is the csv must have a certain format and i can't get to that. I am trying to get this done in postgresql, but if it's not possible i also accept ideas on how to solve it in Java.

I tried a lot of group by syntaxes but it just does not work properly, at this point I'm not even sure it's doable in postgresql but I'd like to do it this way if it's possible. Let's say I want to see all the students from taking a class grouped. I thought it's because the date, but i removed it and it doesn't makes a difference.

SELECT classID,
       firstName || secondName || fathersName,
       studentGender,
       to_char(studentBirthDate, 'DD.MM.YYYY HH24:mm:ss')
FROM Student, Class
WHERE studentGender = Male
GROUP BY classID, firstName, secondName, fathersName, studentGender, sutdentBirthdate
ORDER BY studentID;

I expected following result:

1, PaulLoganDan, JakeIanGolagan, JohnDoeNick, Male, NotSureHere
2, MatthewJoshuaSamuel, JosephJamesBenjamin, AdamLukeHarry, LewisNathanBrad, Male, NotSureHere

What i got:

1, PaulLoganDan, Male, 1.1.1999 \n
1, JakeIanGolagan, Male, 2.2.1999 \n
1, JohnDoeNick, Male, 3.3.1999 \n

2, MatthewJoshuaSamuel, Male, 4.4.1999 \n
2, JosephJamesBenjamin, Male, 5.5.1999 \n
2, AdamLukeHarry, Male, 6.6.1999 \n
2, LewisNathanBrad, Male, 7.7.1999 \n

https://stackoverflow.com/questions/533256/concatenate-multiple-rows-in-an-array-with-sql-on-postgresql — beatrice, Apr 05 '19 at 09:15
Hint: use SQL to select the data you need. Then use Java to display the data the way you want to. Not sure why you select the gender and the birth date if you do'nt need it. — JB Nizet, Apr 05 '19 at 09:15

TheWhiteRabbit · Accepted Answer · 2019-04-05T09:36:08.397

1

I believe the STRING_AGG function is what you are looking for, and is designed for this type of problems. It exists since PostgreSQL 9.0.

I think it should look something like this:

SELECT classID,
       STRING_AGG(firstName || secondName || fathersName, ', ')
FROM Student, Class
WHERE studentGender = Male
GROUP BY classID
ORDER BY studentID;

Or, as of version 8.4, there's the ARRAY_AGG function:

SELECT classID,
       ARRAY_TO_STRING(ARRAY_AGG(firstName || secondName || fathersName), ', ')
FROM Student, Class
WHERE studentGender = Male
GROUP BY classID
ORDER BY studentID;

I don't think there exists an aggregate function for this in older versions.

edited Apr 05 '19 at 09:36

answered Apr 05 '19 at 09:18

TheWhiteRabbit

1,253
1
5
18

Well, i run on a provided VM, from what I see it runs postgresql jdbc version 42.2.5, that means posgresql must be at least 9.5, but I dont have admin acces to see that. Anyway, i tried both STRING_AGG and ARRAY_TO_STRING+ARRAY_AGG and i receive the error [Error Code: 904, SQL State: 42000] ORA-00904: "ARRAY_TO_STRING": invalid identifier. Also got this when trying to use the simple CONCAT function, it's weird.. – d0plan Apr 05 '19 at 10:02
ORA-00904 sounds like you're using an Oracle db instead? Oracle has the LISTAGG and STRING_AGGREGATE functions I believe with similar behavior, perhaps you could try those? – TheWhiteRabbit Apr 05 '19 at 10:04
Oh my.. i feel so stupid now. I was running the query on the oracle database and that's why i kept getting errors.The guy who introduced me to the project forgot to tell me they use both oracle and postgres and bam..Switching to p-sql and i'll try your solution – d0plan Apr 05 '19 at 10:24

Certain format select with group by in postgresql

1 Answers1