1

I have Values which are stored like 1,2,3,4,5 in the database.

I want it back like '1','2','3','4','5'.

I am trying with string_agg(format('''%s''', ticker_information.user_groups), ',') but giving me result '1,2,3,4,5'

Any solution ? Or let me know If I am doing wrong.

Thanks

Jim Jones
  • 18,404
  • 3
  • 35
  • 44
Punit Gajjar
  • 4,937
  • 7
  • 35
  • 70
  • 2
    Don't store your relational data as CSV. Fix your design, and your current question might even not be relevant anymore. – Tim Biegeleisen Jul 26 '21 at 12:59
  • 1
    @TimBiegeleisen, Actually It a stored already by the previous developer. And we cant change the schema now. Its a Live.. – Punit Gajjar Jul 26 '21 at 13:05

2 Answers2

2

You can create an array from your csv string using unnest, wrap the elements with quote_literal() and then aggregate them again. You can achieve this with a subquery ..

SELECT array_to_string(array_agg(i),',') FROM 
  (SELECT quote_literal(unnest(string_to_array(user_groups,','))) 
   FROM ticker_information) j (i);

   array_to_string   
---------------------
 '1','2','3','4','5'

Or with a LATERAL :

SELECT array_to_string(array_agg(quote_literal(j.i)),',') 
FROM ticker_information, 
LATERAL unnest(string_to_array(user_groups,',')) j (i);

  array_to_string
---------------------
'1','2','3','4','5'

Another option would be with regular expressions.. but it could get nasty if the elements of your csv contain commas.

Demo: db<>fiddle

Jim Jones
  • 18,404
  • 3
  • 35
  • 44
2

Try this if you just want a string back with the quotes

WITH sample AS (
  SELECT '1,2,3,4,5'::text as test
)

SELECT
  '''' || array_to_string(
    string_to_array(test, ','),
    ''','''
  ) || ''''
FROM sample
mike.k
  • 3,277
  • 1
  • 12
  • 18