Postreg SQL get All Value with quotes from the comma separated

Question

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

Answer 1

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

Answer 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