Put strings inside quotes with string_to_array()

Question

I am using the following query:

WITH a as (SELECT unnest(string_to_array(animals, ',')) as "pets" FROM all_animals where id = 100)
select * from a

which returns the following data:

1 Cat
2 Dog
3 Bird

My question is, how can I format my string_to_array select above to include single quotes for the returned data to look like this:

1 'Cat'
2 'Dog'
3 'Bird'

Answer 1

Use quote_literal() to safely single-quote strings:

WITH a AS (
   SELECT unnest(string_to_array(animals, ',')) AS pets
   FROM   all_animals
   WHERE  id = 100
   )
SELECT quote_literal(pets) AS pets
FROM   a;

Or shorter without the CTE:

SELECT quote_literal(unnest(string_to_array(animals, ','))) AS pets
FROM   all_animals
WHERE  id = 100;

db<>fiddle here