Postgresql, escape character in string_agg function Question, can I add escape character \ just for fields param_name and value for this?

string_agg(distinct '{name:"' || param_name || '",value:"' || value || '"}',',') as "params",


Result is.

{name:"připojení",value:"1/2""},


I need this (use \ before " only for fields/vars inside "param_name" and "value", in case, when value has " character.

{name:"připojení",value:"1/2\""},


Thanks.

Update, no json, I need export to csv. Some code here.

copy(   

with recursive
cte as(
select
category_id,
category_parent,
category_name::text,
category_id::text category_ids
from s_category as c
where category_parent = 0
union all
select
c.category_id,
c.category_parent,
concat(cte.category_name, ' &gt; ', c.category_name),
concat(cte.category_ids, ':', c.category_id::text)
from s_category as c,cte
where cte.category_id = c.category_parent
)

select distinct
s_product.product_id as "itemID",

...

string_agg(distinct '{name:"' || param_name || '",value:"' || value || '"}',',') as "params",
case when price_tax = price_rec then '' else 'Akce' end as "label"

from
s_product
left join s_cf_j_product_value on s_product.product_id = s_cf_j_product_value.product_id
left join s_product_image on s_product.product_id = s_product_image.product_id
left join s_pricelist_generated_lists on s_product.product_id = s_pricelist_generated_lists.product_id
left join s_producer on s_product.producer_id = s_producer.producer_id
left join s_category on s_product.category_id = s_category.category_id
left join cte on s_product.category_id = cte.category_id
left join s_cf_j_product_value as a on s_product.product_id = a.product_id
left join s_cf_value as v on a.value_id = v.value_id
left join s_cf_param as p on v.param_id = p.param_id

where

...

group by
s_product.product_id,
s_product_image.filename,
s_category.category_id,
s_pricelist_generated_lists.price_tax,
s_pricelist_generated_lists.price_rec,
s_producer.producer_name,
cte.category_name,
cte.category_ids

) TO stdout DELIMITER ',' CSV;


It returns this.

... {name:""připojení"",value:""1/2""""},{name:""připojovací rozteč"",value:""558 mm""},{name:""rozměry"",value:""600 x 960 mm""}",""


But I need this.

... {name:"připojení",value:"1/2\""},{name:"připojovací rozteč",value:"558 mm"},{name:"rozměry",value:"600 x 960 mm"}","
The doubled double-quotes ("") are the necessary consequence of " being the default quote character for COPY with CSV format. The manual:


  QUOTE
  
  Specifies the quoting character to be used when a data value is quoted. The default is double-quote. This must be a single one-byte character. This option is allowed only when using CSV format.
  
  ESCAPE
  
  Specifies the character that should appear before a data character that matches the QUOTE value. The default is the same as the QUOTE
  value (so that the quoting character is doubled if it appears in the
  data). This must be a single one-byte character. This option is
  allowed only when using CSV format.


You could use something like (assuming Postgres 9.1 or later):

COPY (...) TO stdout (FORMAT CSV, QUOTE '\');


Then double-quotes are not escaped.

To ...


  add escape character \ just for fields param_name and value


.. you could simply replace():

string_agg(distinct '{name:"' || replace(param_name, '"', '\"') || '",value:"' || replace(value, '"', '\"') || '"}',',') as "params"


Or:

string_agg(distinct format('{name:"%s",value:"%s"}', replace(param_name, '"', '\"'), replace(value, '"', '\"'), ',') as "params"


But string_agg() has nothing to do with either. And I am not sure what you are trying to achieve.