CODEWITHSUNDEEP
postgresql
RAHUL SONI
RAHUL SONI

Reputation: 1

How to concatenate string_arr in PostgreSQL

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

Upvotes: 0

Views: 65

Answers (1)

user330315
user330315

Reputation:

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

Upvotes: 1

Related Questions