0

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, ' > ', 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"}","
  • 1
    What are you trying to do there? If you want to generate JSON, then generate valid JSON and use the various JSON functions available. – a_horse_with_no_name Aug 7 at 9:14
  • @a_horse_with_no_name No json, I need export to csv, see update. Thanks. – genderbee Aug 7 at 10:01
  • Your two examples contradict each other. Please clarify. And always disclose your version of Postgres. – Erwin Brandstetter Aug 8 at 0:19
0

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.

  • Thank you, exactly what I want, but still have doublequotes in my csv in params, for example {name:""připojení"",value:""1/2\""""}. Other columns are singlequoted by " when needed, so this is ok, just params are doublequoted. Why? If you want more information, I can send whole sql to question. As output I use TO stdout (format csv, DELIMITER ',', quote '"') Thank you. – genderbee Aug 8 at 6:27
  • I mean doubled doublequotes, I need just one doublequotes. – genderbee Aug 8 at 7:06
  • @genderbee: While the QUOTE character is '"' like you display, all double quotes are escaped with the ESCAPE character (also '"' by default), i.e. doubled up. I tried to explain that above. – Erwin Brandstetter Aug 8 at 16:22
  • Ok, thanks for explanation. Is any way to do it without "" in params, just " and \" in case name or value contains " character? I used bash sed function to replace "" with " after dump, but there is a problem, because some other columns contains "" too. – genderbee Aug 12 at 19:58
  • @genderbee: Sure there is a way, but not as COPY output, as it would be contradictory format for this tool. Use replace() as instructed and write out the result .. – Erwin Brandstetter Aug 12 at 21:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.