PostgreSQLの数値文字列変換 - アナログCPU：5108843109

＜20190611 追記＞
何故か最近この記事へのアクセスが急増しています
PostgreSQLは結局ほとんど使っていないので、変なところとかもっと良い方法があるよとかそういうのはどしどし突っ込んでいただけるとありがたいです。
＜/追記＞

まだうまく飲み込めてないのでなんともですが、
PostgreSQLはMySQLに比べると暗黙の型変換をしてくれるところが少ないような気が。
その割に型変換するのに若干クセがあって忘れそうなので、いろいろ試してみたのをメモ。

数値→文字列

たぶん一番よく使うやつ。
TO_CHAR関数に、数値とフォーマットを与え、フォーマットどおりの文字列にしてくれます。

SELECT  
  TO_CHAR(1234, '99999')   -- '  1234'（ゼロ埋めしない＋左に謎のスペース）
 ,TO_CHAR(1234, 'FM99999') -- '1234'（ゼロ埋めしない）
 ,TO_CHAR(1234, '9')       -- ' #'（桁が足りないとこうなる＋左に謎のスペース）
 ,TO_CHAR(1234, 'FM9')     -- '#'（桁が足りないとこうなる）
 ,TO_CHAR(1234, '00000')   -- ' 01234'（左側をゼロ埋め＋左に謎のスペース）
 ,TO_CHAR(1234, 'FM00000') -- '01234'（左側をゼロ埋め）

2番目の「FM999...」のパターンを一番よく使うかなと思うのですが、充分な桁数が必要というのが厄介。
仕様上最大になる桁数で書いておけばいいんですが、桁が大きくなる場合はあんまりスマートじゃない気が…

続いて負数と小数のいろいろ。

SELECT  
  TO_CHAR( 1234.5, '99999')       -- '  1235'
 ,TO_CHAR(-1234.5, '99999')       -- ' -1235'
 ,TO_CHAR(-1234.5, 'FM99999.99')  -- '-1234.5'
 ,TO_CHAR(-1234.5, 'FM99999.00')  -- '-1234.50'

FMを付けない場合、謎の左側スペースのうち
ひとつは符号用、残りは単にゼロで埋める代わりにスペースが入ってるだけ…みたいですね。
そして整数化するときは四捨五入されています。
小数として扱う場合は一番下のパターンをよく使うことになるかな。

その他のフォーマットも可能です。
クエリ内で必要にならない限りは使わないと思うけど。
（「見た目に関わる内容はsmartyやjsでやるべき（どうしてもという場合はPHP）」派）

SELECT  
  TO_CHAR(1234567, 'FM999,999,999') -- '1,234,567'（カンマ区切り）
 ,TO_CHAR(    123, '[9999]')        -- '[  123]'（何かで囲んでみる）
 ,TO_CHAR(    123, '[FM9999]')      -- '[123]'（↑だと案の定スペース入ったのでFM付ける）
 ,TO_CHAR(    123, 'FM[9999]')      -- '[123]'（↑位置を変えてみても大丈夫だった）
 ,TO_CHAR(    123, '[9999]FM')      -- '[123]'（↑最後でもええんかい！）
 ,TO_CHAR(    123, '/9/99/99/')     -- '/ /  1/23/'（使いどころはわからないが動く）
 ,TO_CHAR(    123, 'FM/9/99/99/')   -- '//1/23/'（↑のスペース削除版）
 ,TO_CHAR(   -123, '/9/99/99/')     -- '/ / -1/23/'（負数だとこんな感じ）

文字列→数値

今度は数値への変換なので
TO_NUMBER関数に文字列とフォーマットを与え、
フォーマットどおりに読んだ結果を返してくれます。

SELECT
  TO_NUMBER('123456'  , '9999999')     -- '123456'（素直にそのまま）
 ,TO_NUMBER('123456'  , '9')           -- '1'（桁数が足りないと指定桁数分だけ左から読む）
 ,TO_NUMBER('12_34_56', '99_99_99')    -- '123456'（変な形式の場合はフォーマットでも同様に指定）
 ,TO_NUMBER('12_34_56', '999999')      -- '1234'（桁数が足りないと左から読み、数字だけ抽出する模様）
 ,TO_NUMBER('12_34_56', '999999999')   -- '123456'（桁数が十分あればいい感じに数字だけ抽出する模様）
 ,TO_NUMBER('12_34.56', '999999999')   -- '123456'（勝手に小数と認識してくれたりはしない）
 ,TO_NUMBER('12_34.56', '9999999.999') -- '1234.56'（小数として扱いたいときはドットを適当な位置へ）
 ,TO_NUMBER('-2_34.56', '9999999.999') -- '-234.56'（負数は認識してくれる）

参考
PostgreSQLで to_char()すると前に半角スペースが入る - かわろぐ
 https://www.postgresql.jp/document/9.4/html/functions-formatting.html