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?

bphi · Accepted Answer · 2019-05-07 20:12:02Z

9

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

edited May 7, 2019 at 20:12

bphi

3,1753 gold badges30 silver badges41 bronze badges

answered Mar 14, 2017 at 3:28

cha

10.4k1 gold badge20 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user330315 Over a year ago

Or simply cast to ::text

Collectives™ on Stack Overflow

postgresql string agg numeric/integer output result is wrong

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related