How to replace comma with apostrophe/single quote?

Question

How do I replace comma with apostrophe/single quote in pgsql select query? My current query is:

SELECT array_to_string(array_agg(districtname), ', ') as dnames
FROM districts
where id in ('' || Replace((select districtIds from tblmtr where id = 1), ',' , ''',''') || '');

It is returning null.
Id's in table tblmtr are like this 2,3,4

When I change it to only 2 or 3 or 4 it returns the correct result.
Result should be like this: district1name,district2name,district3name

Answer 1

A couple of things are not ideal here:

• Upgrade to a modern version of Postgres. Version 8.4 reached EOL in 2014.

• Have a look at database normalization and reconsider your relational design. It's typically a very bad idea to store multiple ID values in a comma-separated string.

• IN would expect a subquery or a list of values. You seem to try and provide a string containing the values, which does not work. Consider a JOIN instead of your awkward IN construct.

While stuck with your unfortunate design, convert the comma-separated string to an array on the fly and use the ANY construct.
You neglected to provide your table definitions, so making a few assumptions.

SELECT array_to_string(array_agg(d.districtname), ', ') AS dnames
FROM   tblmtr    t 
JOIN   districts d ON d.id = ANY (string_to_array(t.districtIds, ','))
WHERE  t.id = 1;

Or:

SELECT ARRAY (
   SELECT d.districtname
   FROM   tblmtr    t 
   JOIN   districts d ON d.id = ANY (string_to_array(t.districtIds, ','))
   WHERE  t.id = 1
   ) AS dnames;