PostgreSQLの文字列リテラルでは、バックスラッシュ文字の意味が
standard_conforming_stringsという設定によって変わるようです。
standard_conforming_stringsが"off"のときは、バックスラッシュは特殊文字として
機能します。「\n」はAscii文字のLFで、「\t」はTABとして解釈されます。
一方、standard_conforming_stringsが"on"のときは標準SQL仕様準拠モードとなり、
バックスラッシュはただの「バックスラッシュという文字そのもの」として解釈されます。
「\n」は「\」と「n」の２文字となります。
このあたりことは以下のページの方が詳しいですのでそちらをどうぞ。
PostgreSQLは標準でバックスラッシュをエスケープしない仕様になった | 徳丸浩の日記
元々、標準のSQL仕様的には、バックスラッシュは特殊文字扱いしないのが正解とのことで、
PostgreSQLはその標準仕様に従うようにしたとのことです。
そうなると、標準SQL仕様としては文字列中にLFやTABを記載したいときにはどうするのが筋なんでしょうか？
調べても公式っぽい文書を見つけられなかったのですが、どうやらCHR()関数を使うことになるようです。
select 'hoge' + CHR(10)/* CR */ + 'fuga' + CHR(9) /* TAB */
ただ、これが正式とはいえ記述するのが面倒なので、PostgreSQLではE''文字列構文という表記で
これまでどおりの書き方ができるようになっています。
select E'hoge\nfuga\t'
それと、この""の表記に関して気になった動作があったので以下にメモしておきます。
psql=# \pset expanded on
Expanded display is on.
psql=#
psql=# set standard_conforming_strings = on;
SET
psql=#
psql=# select
psql-# value,
psql-# replace(value, '\r', '[CR]') as "bs",
psql-# replace(value, '\\r', '[CR]') as "bsbs",
psql-# replace(value, E'\r', '[CR]') as "E",
psql-# regexp_replace(value, '\r', '[CR]', 'g') as "regex",
psql-# regexp_replace(value, E'\r', '[CR]', 'g') as "regex E"
psql-# from
psql-# (select E'test\\ value \rhoge\rfuga\r\nbar'::text as value) a
psql-# ;
-[ RECORD 1 ]-----------------------------
value | test\ value \rhoge\rfuga\r
| bar
bs | test\ value \rhoge\rfuga\r
| bar
bsbs | test\ value \rhoge\rfuga\r
| bar
E | test\ value [CR]hoge[CR]fuga[CR]
| bar
regex | test\ value [CR]hoge[CR]fuga[CR]
| bar
regex E | test\ value [CR]hoge[CR]fuga[CR]
| bar
psql=# set standard_conforming_strings = off;
SET
psql=#
psql=# select
psql-# value,
psql-# replace(value, '\r', '[CR]') as "bs",
psql-# replace(value, '\\r', '[CR]') as "bsbs",
psql-# replace(value, E'\r', '[CR]') as "E",
psql-# regexp_replace(value, '\r', '[CR]', 'g') as "regex",
psql-# regexp_replace(value, E'\r', '[CR]', 'g') as "regex E"
psql-# from
psql-# (select E'test\\ value \rhoge\rfuga\r\nbar'::text as value) a
psql-# ;
WARNING: nonstandard use of escape in a string literal
LINE 3: replace(value, '\r', '[CR]') as "bs",
^
HINT: Use the escape string syntax for escapes, e.g., E'\r\n'.
WARNING: nonstandard use of \\ in a string literal
LINE 4: replace(value, '\\r', '[CR]') as "bsbs",
^
HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
WARNING: nonstandard use of escape in a string literal
LINE 6: regexp_replace(value, '\r', '[CR]', 'g') as "regex",
^
HINT: Use the escape string syntax for escapes, e.g., E'\r\n'.
-[ RECORD 1 ]-----------------------------
value | test\ value \rhoge\rfuga\r
| bar
bs | test\ value [CR]hoge[CR]fuga[CR]
| bar
bsbs | test\ value \rhoge\rfuga\r
| bar
E | test\ value [CR]hoge[CR]fuga[CR]
| bar
regex | test\ value [CR]hoge[CR]fuga[CR]
| bar
regex E | test\ value [CR]hoge[CR]fuga[CR]
| bar
「\r」(CR)が含まれる文字列に対する置換をしているのですが、
replace()とregexp_replace()で検索語に'\r'と記述した場合の動きが違います。
replace()では、standard_conforming_strings=onとした場合に'\r'という表記では
置換されていません。これは、「\」と「r」の２文字と解釈されているのでCRとはマッチしません。
一方、regexp_replace()の場合では同じ'\r'でもCRとヒットしています。
これはおそらく正規表現としての表記において「\」「r」という２文字をCRと解釈するようになっているからのようです。