How to concatenate string_arr in PostgreSQL

Question

How to concatenate rcm_working_paper and documents code is below-

select  RCM.id as rcm_no,  
       string_agg(distinct RWP.body->>'working_paper_code', ',')as rcm_working_paper,   
       string_agg(distinct RWP.body->>'document', ',')as documents
from masters."RCM" RCM
  inner join  masters."RCM_WORKING_PAPERS" RWP on RCM.id=RWP.rcm_id
GROUP BY RCM.id

I am doing that way and occurred some error.

select  
    RCM.id as rcm_no,  
    string_agg(distinct RWP.body->>'working_paper_code'|| ' ' || RWP.body->>'document', ',')as 
    rcm_working_paper
from masters."RCM" RCM
  inner join  masters."RCM_WORKING_PAPERS" RWP on RCM.id=RWP.rcm_id
GROUP BY RCM.id

ERROR: operator does not exist: text ->> unknown LINE 3: ... RWP.body->>'working_paper_code'|| ' ' || RWP.body->>'docume... ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. SQL state: 42883 Character: 115 — RAHUL SONI
– RAHUL SONI, Commented Jan 21, 2020 at 11:01

user330315 · Accepted Answer · 2020-01-21 11:08:46Z

1

You need to enclose each expression in parentheses:

select  
  RCM.id as rcm_no,  
  string_agg(distinct (RWP.body->> 'working_paper_code')|| ' ' ||(RWP.body->>'document'), ',') as rcm_working_paper
from masters."RCM" RCM
  inner join  masters."RCM_WORKING_PAPERS" RWP on RCM.id=RWP.rcm_id
GROUP BY RCM.id

Alternatively use concat_ws()

select  
   RCM.id as rcm_no,  
   string_agg(distinct concat_ws(' ', RWP.body->> 'working_paper_code', RWP.body->>'document'), ',') as rcm_working_paper
from masters."RCM" RCM
  inner join  masters."RCM_WORKING_PAPERS" RWP on RCM.id=RWP.rcm_id
GROUP BY RCM.id

Or concat()

select  
   RCM.id as rcm_no,  
   string_agg(distinct concat(RWP.body->> 'working_paper_code', ' ', RWP.body->>'document'), ',') as rcm_working_paper
from masters."RCM" RCM
  inner join  masters."RCM_WORKING_PAPERS" RWP on RCM.id=RWP.rcm_id
GROUP BY RCM.id

answered Jan 21, 2020 at 11:08

user330315

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

1 Comment

Jim Jones Over a year ago

@RAHULSONI if this answer solved your problem, please mark it as resolved.

Collectives™ on Stack Overflow

How to concatenate string_arr in PostgreSQL

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