Postgres regex matching multiple characters

Question

I'm trying to build a regex to match multiple characters in order to replace them.

I have a PHP-working pattern which looks like :

/([\{\}\"])*/g

But unfortunately, it doesn't seem to work with Postgres.

Could someone help please ?

Here's the request I'm trying to build :

SELECT 
   regexp_replace(array_agg(DISTINCT columnName)::TEXT, '/([\{\}\"])*/g', '')
FROM tableName
WHERE ...

Answer 1

Not sure what the / and /g are in your regex, if that is a delimiter and the "global" flag, then you can't specify that inside the regex in Postgres. In Postgres that would be an extra parameter to the function:

regexp_replace(array_agg(DISTINCT columnName)::TEXT, '([\{\}\"])*', '', 'g')

but it looks like you are trying to concatenate strings. If that is true, I think you actually want:

string_agg(distinct columnName, '')