【PostgreSQL】大文字小文字を区別せずに置換

table_nameテーブルのcolumn_1に含まれる大文字小文字ごちゃまぜのhogehogeを
全部まとめてfugafugaに置換したい時の処理について。
とりあえず正解から。

UPDATE table_name SET column_1 = REGEXP_REPLACE(column_1, '(?i)hogehoge', 'fugafuga') WHERE column_1 ILIKE '%hogehoge%'

以下解説。

前提としてPostgreSQLは定数の大文字小文字を区別します。
(MySQLとかだと区別しないらしいですが…)
そこで、まずWHERE句の部分一致でひっかけるために、LIKEではなくILIKEを使います。
これだと大文字小文字を区別しません。

次に、置換。ここでも大文字小文字対応をしてあげる必要があります。
最初にやったのがこちら

REPLACE(lower(column_1), 'hogehoge', 'fugafuga')

これだと一見大丈夫そうですがhogehoge以外の部分も小文字になってしまいます。
じゃあ正規表現とかでできないの？ということで試したのがこれ

REGEXP_REPLACE(column_1, /hogehoge/i, 'fugafuga')

これはぜんぜん駄目。ポスグレの正規表現はスラッシュで囲むんじゃなくて
普通にシングルクォートで囲みます。

オプションをつけるときは、頭に (?オプション) とするそうな。

ということで、最終的に一番上の正解例の形になりましたとさ。