I have this array:
ARRAY['this', 'is', 'my', 'string']
I would like to convert it into a string like this:
'this', 'is', 'my', 'string'
I tried:
array_to_string(ARRAY['this', 'is', 'my', 'string'], ',')
But instead of the desired output, I get this:
this, is, my, string
Unnest the array, modify (add quotes to) all its elements and aggregate them into a string:
select string_agg(format('%L', elem), ',')
-- or
-- select string_agg(quote_literal(elem), ',')
from unnest(array['this', 'is', 'my', 'string']) elem
string_agg
---------------------------
'this','is','my','string'
(1 row)
quote_literal function instead format can be better - or the placeholder %L for format function can be used.
quote_literal() is probably more elegant, thanks.
You'll have to wrap the items of the array in single quotes yourself using character escaping, or by pre-processing the array to wrap the items in single quotes. Try this
array_to_string(ARRAY['''this''','''is''','''my''','''string'''], ',')
||. So I think you meant to say '''' || array_to_string(ARRAY['this', 'is', 'my', 'string'], ''', ''') || ''''.
\' to '' everywhere in your query, you will have a good solution.
