I am trying to make below query compatible with Postgres, which is originally written for Oracle
select concat(s.udise_sch_code ,tch.teacher_id) comp_id,
s.sch_id as sch_id,
s.udise_sch_code as sch_code,
s.school_name as school_name,
v.latitude as latitude,
v.longitude as longitude,
tch.teacher_id as tch_id,
tch.name as tch_name,
tch.social_category as social_cat,
to_char(tch.startdate,'yyyy-MM-dd-HH:mm') as startdate,
**LISTAGG(DISTINCT phone_no, ',') WITHIN GROUP (ORDER BY phone_no) AS phone_no,**
v.village_id as village_id,
v.village_name as village_name,
v.sch_id as vsch_id,
LISTAGG(DISTINCT (r.rolerosterid||','||to_char(r.rolerosterstartdate,'yyyy-MM-dd-HH:mm')||',' ||to_char(r.rolerosterenddate,'yyyy-MM-dd-HH:mm')), '|') WITHIN GROUP (ORDER BY r.rolerosterid) combinedrosterentry
from school s
LEFT JOIN teacher tch on s.sch_id = tch.sch_id
LEFT JOIN village v on s.sch_id = v.sch_id
LEFT JOIN contact c on tch.teacher_id = c.teacher_ids
LEFT JOIN rosterentry r on tch.teacher_id = r.teacher_ids
group by c.teacher_ids,concat(s.udise_sch_code ,tch.teacher_id), s.sch_id, s.udise_sch_code, s.school_name, v.latitude,
v.longitude, tch.teacher_id, tch.name, tch.social_category, to_char(tch.startdate,'yyyy-MM-dd-HH:mm'),
v.village_id, v.village_name, v.sch_id
I am getting below error
ERROR: cannot use DISTINCT with WITHIN GROUP
LINE 11: LISTAGG(DISTINCT phone_no, ',') WITHIN GROUP (ORDER ...
It looks like DISTINCT is not supported with WITHIN GROUP. I would be needing help to understand and resolve this issue, in order to make it work for Postgres (version 13)