'Postgresql put strings inside quotes with array_to_string

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?

arrayspostgresqlstring-aggregation


Solution 1:[1]

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;

Solution 2:[2]

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

Solution 3:[3]

You can use string_agg() function with '''; ''' so it will be like

SELECT string_agg(tag_name, '''; ''') from my_table

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Adeel
Solution 2
Solution 3 Vaghinak

Related Questions