postgresql string agg numeric/integer output result is wrong

Question

As the title say, i need to concat the result query with string agg using this query (Without String Agg)

select pdet.dept_id, pdet.grade_id
from psa_aso_target ptar
inner join psa_aso_targetdetails pdet on pdet.target_id=ptar.target_id and ptar.branch_id='18'

and the result is this

and then i add string agg

select pdet.dept_id, string_agg(distinct pdet.grade_id::char,'|') as grade
from psa_aso_target ptar
inner join psa_aso_targetdetails pdet on pdet.target_id=ptar.target_id and ptar.branch_id='18'
group by pdet.dept_id

and i expect the outcome result is

dept_id | grade_id
      2 | 1|2|3
      3 | 4|13|14|15|18
      5 | 6|10|17
     63 | 2|4|7

But the result i get is

dept_id | grade_id
      2 | 1|2|3
      3 | 1|4
      5 | 1|6
     63 | 2|4|7

any idea?

score 8 · Accepted Answer · edited May 07 '19 at 20:12

It is because you cast the numeric value to char(1). By casting it to char(1) you truncate the value to 1 character only, so 13, 14, 15, 18 all become 1

You need to cast it to varchar, or text:

select pdet.dept_id, string_agg(distinct pdet.grade_id::varchar,'|') as grade
from psa_aso_target ptar
inner join psa_aso_targetdetails pdet on pdet.target_id=ptar.target_id and ptar.branch_id='18'
group by pdet.dept_id

select pdet.dept_id, string_agg(distinct pdet.grade_id::text,'|') as grade
from psa_aso_target ptar
inner join psa_aso_targetdetails pdet on pdet.target_id=ptar.target_id and ptar.branch_id='18'
group by pdet.dept_id

Thanks to the commenters below for contributing to the answer

postgresql string agg numeric/integer output result is wrong

1 Answers1