In select I have used array_to_string like this (example)
array_to_string(array_agg(tag_name),';') tag_names
I got resulting string "tag1;tag2;tag3;..." but I would like to get resulting string as "'tag1';'tag2';'tag3';...".
How can I do this in Postgres?
Use the functions string_agg() and format() with the %L placeholder, which quotes the argument value as an SQL literal.
with my_table(tag_name) as (
values
('tag1'),
('tag2'),
('tag3')
)
select string_agg(format('%L', tag_name), ';' order by tag_name) tag_names
from my_table;
tag_names
----------------------
'tag1';'tag2';'tag3'
(1 row)
Alternatively, format('%L', tag_name) may be replaced with quote_nullable(tag_name).
Or your can use unnest, format, array_agg and array_to_string in one request like this :
select array_to_string(t.tag, ',')
from (
select array_agg(format('%L', t.tag)) as tag
from (
select unnest(tag_name) as tag
) t
) t;
Or use
array_to_string(array_agg(''''||tag_name||''''),';') tag_names
or even simpler (thanks for the commenting :) )
string_agg(''''||tag_name||''''),';') tag_names
Note:
When dealing with multiple-argument aggregate functions, note that the ORDER BY clause goes after all the aggregate arguments. For example, write this:
SELECT string_agg(a, ',' ORDER BY a) FROM table;
not this:
SELECT string_agg(a ORDER BY a, ',') FROM table; -- incorrect
See https://www.postgresql.org/docs/current/static/sql-expressions.html#SYNTAX-AGGREGATES
You can use string_agg() function with '''; ''' so it will be like
SELECT string_agg(tag_name, '''; ''') from my_table
array_to_string(array_agg(...))can be simplified to tostring_agg()