REGEXP_REPLACE()でdoes not existsになる現象の調査と結果

AWS Redshiftで実行していたSQLを検証環境用のDB(Postgresql@9.6)で実行したところエラーがでて困った話し。
エラー内容と実行したSQLは↓のようなイメージ。

SELECT REGEXP_REPLACE(json, '\\{|\\}') AS `json_data` FROM `log_table` ;


ERROR:  function regexp_replace(text, unknown) does not exist
行 1:   REGEXP_REPLACE(json, '\\{|\\...
                              ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

結論:

原因は2つ。

• REGEXP_REPLACE()の第二引数patternがunknownになっているのでわかると思うが'\\{|\\}'が文字列として認識できていない。
• PostgresqlのREGEXP_REPLACE()は第三引数までが必須引数。

文字列として認識できない問題の解決策:

Hintにあるようにunknownになっている第二引数を文字列で型キャストしてやる。
型キャストする方法は2つある？みたいで[カラム名]::[型名]かCAST([カラム名] AS [型名])してやればいい(後者は試していない)

SELECT REGEXP_REPLACE(json, '\\{|\\}'::text) AS `json_data` FROM `log_table` ;

これでERROR:と表示された行が解消した。

PostgresqlのREGEXP_REPLACE()は第三引数までが必須引数。

全ての元凶はこれだった。
PostgresにもREGEXP_REPLACEあるじゃん！とPostgresのドキュメントで確認していたのだけど引数までは確認していなくてしばらくハマった。

REGEXP_REPLACE 関数では第三引数は省略可能だがPostgresの
9.4. 文字列関数と演算子のreqexp_replaceの項目では第三引数は省略可能でないのが確認できる。

ということで第三引数を指定(今回は{}を除去したかったので空文字を指定)することで解決した。

2つ原因があったことに気づくまでに時間がかかってしまったのが反省点。
SQLの方言にたまに遭遇すると気づくまでに時間がかかるのをなんとかしたい。