Concat values to string array postgres

Question

I want to reach following structure:

["687ccca","da075bd","4194d"]

and try to achieve it like this:

UPDATE table1
SET "ids"= CONCAT  ('[', result, ']')
FROM (SELECT string_agg(  id::character varying, ', ')
FROM   table2 where "name" like '%W11%'
or "name" like '%12%'
or "name" like '%13%'
or "name" like '%5%'
or "name" like '%W9%'
or "name" like '%74%'
) AS result
WHERE "ids"='all';

however I get this:

[("df6bd58d, 26e094b, 637c1, 4a8cf387ff43c5, 9b0bf9f")]

How do I remove ( and ) and add " after each id?

Maybe like this? CONCAT('["', string_agg(id::character varying, '", "'), '"]') — m1k1o
– m1k1o, Commented Jul 24, 2019 at 10:07

S-Man · Accepted Answer · 2019-07-24 10:08:36Z

1

I believe you want to get an JSON array:

demo:db<>fiddle

SELECT
    json_agg(your_texts)
FROM
    your_table

If you really want text you can cast this result with ::text into a text afterwards:

json_agg(your_texts)::text

answered Jul 24, 2019 at 10:08

S-Man

24k9 gold badges51 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user122222 Over a year ago

You're hero! Thank you!

Collectives™ on Stack Overflow

Concat values to string array postgres

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related